1. Function
오라클에서 Select쿼리에 함수를 바로 사용하지 말고 스칼라 서브쿼리를 사용하자. 스칼라 서브쿼리는 같은 인풋값이면 같은 아웃풋 값을 출력한다.
- 스칼라 서브쿼리
SELECT
emp.employee_id,
emp.salary,
(SELECT MAX(salary)
FROM employees sub
WHERE sub.department_id = emp.department_id) AS dept_max_sal
FROM employees emp;
스칼라 서브쿼리의 경우 결과 값을 오라클 내부에 캐시로 저장한다. 따라서 같은 인풋 값인 경우 캐시에서 바로 아웃풋 값을 출력한다.
만약 스칼라 서브쿼리 수행 도중 값이 변경되면 어떻게 될까?
위의 Select절 쿼리가 수행되는 도중 employee_id가 추가됐다고 가정하자. 추가된 값이 반영이 될까?
정답은 아니다. 오라클에선 MVCC를 통해 다중버전 관리를 하므로 Select가 수행된 시점의 데이터를 기준으로 쿼리를 수행한다.
MVCC란?
Multi-Version Concurrency Control. 다중 버전 동시성 제어라고 한다.
각 트랜잭션마다 스냅샷을 찍어놓고 그것을 기준으로 쿼리를 수행한다. Select쿼리가 수행되는 도중 값의 변경이 생기더라도 스냅샷은 Select쿼리가 수행되기 전 DB상태가 기준이므로 값이 바뀌지 않는다.
2. INSERT /*+ APPEND */ INTO와 INSERT INTO의 차이
들어가기 전 오라클의 High Water Mark에 대해서 알아야한다. HWM이란 데이터가 저장된 끝 부분의 위치를 가리킨다. 새로운 데이터가 삽입된다면 HWM뒤에 삽입하면 된다.
문제는 삽입과 삭제가 빈번해진다면 HWM앞에 빈 블록들이 넘쳐나서 공간이 낭비가 된다. HWM뒤에 넣지 않고 새로운 데이터를 빈 공간에 하나씩 넣으면 공간을 더욱 효율적으로 사용한다.
또한 HWM의 경우 FULL TABLE SCAN시 HWM위치까지 스캔하므로 빈 공간이 많을경우 시간도 많이 걸린다.
1. INSERT /*+APPEND*/INTO
- 데이터를 버퍼캐시를 통해 기록하지 않고 직접 기록한다.
- HWM뒤에 바로 넣고 버퍼 캐시를 들리지 않는다.
2. INSERT INTO
- 데이터를 버퍼캐시에 기록한 뒤 디스크에 기록한다.
- HWM 전 빈 공간에 데이터를 삽입한다.
힌트를 사용하는 것이 더 빠르지만 상황에 맞게 적절한 선택을 하면 된다.
3. 파티션
100억개의 데이터를 가진 테이블이 있다. 이 테이블에 대해서 조회, 삭제, 수정 작업을 효율적으로 진행하려면 어떻게 해야 할까?
여러 방법이 있겠지만 초기 설계시 파티션 테이블로 설계하는 방법이 있다.
파티션 테이블이란?
하나의 테이블을 논리적으로 분할하여 관리가 편하게 설계한 것. 100억 개의 데이터가 있다면 12개의 파티션을 만들어서 1월부터 12월까지 데이터를 넣고 관리할 수 있다.
- 장점
1. 검색
100억개의 데이터 중 5월에 해당하는 데이터가 필요하다고 치자. 파티션 프루닝을 통해서 파티션 테이블에서 5월 파티션 부분에서만 Select쿼리를 수행하면 된다.
2. 삭제
100억개의 데이터 중 일부 데이터를 삭제해야 한다. 만약 월 단위로 떨어지는 데이터라면 해당 파티션을 DROP 하면 된다. Delete보다 훨씬 빠르다.
로컬 인덱스와 글로벌 인덱스
파티션 내 인덱스를 사용하는 방법 2가지가 있다.
1. 로컬 인덱스
-파티션마다 새로운 인덱스를 만들어서 관리하는 방법
-특정 파티션만 사용할 때 성능이 좋다.
2. 글로벌 인덱스
-전체 파티션에서 통용되는 인덱스를 하나 만들어서 관리하는 방법
-모든 파티션의 데이터를 대상으로 하는 쿼리에서 성능이 좋다.
-파티션 추가 삭제시 인덱스를 관리해야 한다.
상황에 따라 다르지만 내 생각으로는 로컬 인덱스를 사용하는 것이 맞다. 데이터의 검색, 삭제, 백업 등 분할해서 관리하려고 파티션 테이블을 사용했는데 인덱스는 글로벌 인덱스를 사용한다? 잃는 것이 더 많다. 물론 로컬, 글로벌인덱스 모두 만들어놓고 사용할 수 있지만 관리비용도 증가하고, 옵티마이저가 항상 최적의 인덱스를 타줄지 장담할 수 없다.
고로 파티션 테이블을 만들었다면 로컬 인덱스만 사용하자. 글로벌 인덱스를 고려해야 한다면 잘 비교해 보고 사용하자
- 참고
https://meongmeongi.tistory.com/9
'IT > DB' 카테고리의 다른 글
| Connection reset by peer 오류 해결기 (0) | 2025.01.07 |
|---|---|
| 결합 인덱스란? (0) | 2024.12.24 |