5.3 인덱스 이용한 소트 연산 생략
인덱스는 항상 키 컬럼순으로 정렬된 상태를 유지하므로,
이를 활용하면 SQL의 ORDER BY 또는 GROUP BY 절이 있어도 생략이 가능하다.
5.3.1 Sort Order By 생략
인덱스 선두 컬럼을 (종목코드 + 거래일시) 순으로 구성하지 않으면 소트 연산 생략이 불가능하다.
select 거래일시, 체결건수, 체결수량 ...
from 종목거래
where 종목코드 = 'KR123'
order by 거래일시
종목코드 = ‘KR123’ 조건을 만족하는 레코드를 인덱스에서 모두 읽어야 하고,
그만큼 테이블 랜덤 액세스가 발생한다. 다 읽고 거래일시 순으로 정렬해야 하므로 OLTP 환경에서는 좋지 않다.
5.3.2 Top N 쿼리
Top N 쿼리는 전체 결과 집합 중 상위 N개 레코드만 선택 하는 쿼리.
select TOP 10 거래일시 ...
from 종목거래
where 종목코드 > 'KR123'
order by 거래일시
...
...
...
소트를 생략할 수 있도록 인덱스 구성해 주더라도 중간집합을 만들어야 하므로
부분범위 처리는 불가능해보이지만, 종목코드 + 거래일시 순으로 구성된 인덱스를 이용하면
옵티마이저는 소트 연산을 생략한다.
실행계획을 확인하면
STOPKEY가 보이는데 레코드가 아무리 많아도 ROWNUM으로 지정한 건수만큼 결과 레코드를 얻으면 멈춘다는 뜻이다.
0 SELECT STATEMENT
1 0 COUNT (STOPKEY)
2 1 VIEW
3 2 TABLE ACCESS ...
4 3 INDEX (RANGE SCAN) ...
5.3.3 최소/최대값 구하기
최소값 또는 최대값 구하는 실행 계획을 보면 Sort Aggregate 오퍼레이션이 나온다.
Sort Aggregate 위해 전체 데이터를 정렬하지는 않지만, 전체 데이터를 읽으면서 값을 비교한다.
SELECT MAX(SAL) FROM EMP;
EXPLAIN PLAN
0 SELECT ...
1 0 SORT (AGGREGATE) ...
2 1 TABLE ACCESS (FULL) ....
인덱스는 정렬 되어 있으므로 전체 데이터를 읽지 않고도 최소 또는 최대값을 쉽게 찾을 수 있음.
맨 왼쪽 내려가서 첫 번쨰 읽는 값이 최소값이고, 맨 오른쪽으로 내려가서 읽는 값이 최대값이다.
루트와 브랜치 블록에서 맨 마지막 레코드가 가리키는 값이 최대값이다.
인덱스를 이용한 최대값 찾을때 실행계획
SELECT MAX(SAL) FROM EMP;
0 SELECT ..
1 0 SORT(AGGREGATE) ...
2 1 INDEX (FULL SCAN (MIN/MAX)) OF ....
인덱스 이용해 최소/최대값 구하기 조건
전체 데이터를 읽지 않으려면 조건절 컬럼과 MIN/MAX 함수 인자 컬럼이 모두 인덱스에 포함되어야 한다.
(액세스가 발생되지 않아야함).
CREATE INDEX EMP_X1 ON EMP(DEPTNO, MGR, SAL);
SELECT MAX(SAL) FROM EMP WHERE DEPTNO = 30 AND MGR = 7698;
PLAN ...
0 SELECT ...
1 0 SORT(AGGREGATE) ...
2 1 FIRST ROW ...
3 2 INDEX (RANGE SCAN (MIN/MAX)) ...
조건절 컬럼과 MAX 걸럼이 모두 인덱스 포함되어있고,
인덱스 선두 컬럼이 DEPTNO, MGR 이 등치 조건이므로 가장 오른쪽에 있는 값 하나를 읽는다.
‘FIRST ROW’는 조건을 만족하는 레코드 찾으면 멈춘다는 뜻을 의미. (FIRST ROW STOPKEY)
Top N 쿼리를 이용해 최소/최대값 구하기
실행계획에 STOPKEY 작동하는 알고리즘은 모든 컬럼이 인덱스 포함되지 않아도 잘 작동한다.
SELECT *
FROM (
SELECT sal
FROM emp
WHERE deptno = 30
AND mgr = 7698
ORDER BY sal DESC
)
WHERE rownum <= 1;
--------------
0 SELECT STATEMENT
1 0 COUNT (STOPKEY)
2 1 VIEW
3 2 TABLE ACCESS
4 3 INDEX (RANGE SCAN DESCENDING)
위 SQL에서 MGR 컬럼이 인덱스에 없지만,
가장 큰 SAL 값을 찾기위해 DEPTNO = 30 조건을 만족하는 ‘전체’ 레코드를 읽지 않는다.
인라인 뷰를 사용하므로 쿼리가 약간 더 복잡하지만, 성능 측면에서 MIN/MAX 쿼리보다 낫다.
5.3.4 이력조회
변경 이력을 관리하는 테이블
단순한 이력조회
이력 조회할때 ‘First Row StopKey’ or ‘Top N StopKey’ 알고리즘이 작동 될 수 있다.
단순한 조회 패턴은
select 장비번호, 장비명 ..
,(SELECT MAX(변경일자) ...) 최종변경일자
from 장비 p
where 장비구분코드 = 'A001';
---- ---- ---- ---- ----
1 SORT AGGREGATE
2 FIRST ROW
3 INDEX RANGE SCAN (MIN/MAX)
4 TABLE ACCESS BY INDEX ROWID..
5 INDEX RANGE SCAN ...
스칼라 서브 쿼리 부분에 ‘FIrst Row StopKey’ 알고리즘 작동한다.
인덱스를 (장비번호 + 변경일자 + 변경순번) 으로 구성되어있기에 가능하다.
INDEX_DESC 힌트 활용
인덱스를 역순으로 읽도록 index_desc 힌트를 하용하고,
첫 번째 레코드에서 멈추도록 rownum <= 1 조건절 사용한다.
SELECT 장비명, 장비번호
, SUBSTR(최종이력, 1, 8) 최종변경일자 .....
FROM (
SELECT 장비번호, 장비명,
(SELECT /*+ INDEX_DESC(X 상태변경이력_PK) */
변경일자 || LAPD(변경순번, 4) || 상태코드
FROM 상태변경이력 X
WHERE 장비번호 = p.장비번호
AND ROWNUM <= 1) 최종이력
FROM 장비 P
where 장비구분코드 = 'A001'
)
----------------------------------------------------------------
0 SELECT STATEMENT
1 COUNT STOPKEY
2 TABLE ACCESS BY INDEX ROWID
3 INDEX RANGE SCAN DESCEDING
4 TABLE ACCESS BY INDEX ROWID
5 INDEX RANGE SCAN
이 방식이 성능은 확실히 좋지만, 인덱스 구성이 완벽해야지만 쿼리가 잘 작동한다.
인덱스 구성이 바뀌면 언제든 결과집합에 문제가 생길수있지만 PK 구성을 변경하는 일은 거의 없으므로 문제가 되지 않는다.
5.3.5 Sort Group By 전략
인덱스를 이용해 소트 연산 생략 가능한건 이해하기 쉽다. 그루핑 연산에도 인덱스 활용할 수 있다.
select region, avg(age), count(*)
from customer
group by region
----------------------------------------
0 SELECT STATMENT
1 SORT GROUP BY NOSORT
2 TABLE ACCESS BY INDEX ROWID
3 INDEX FULL SCAN
SQL 에 region 선두 컬럼인 인덱스 이용하면 Sort Group By 연산을 생략할 수 있다.
Sort Group By 연산 실행계획 (Array Size = 3)
- 인덱스 A 구간을 스캔하면서 테이블을 액세스 하다가 B를 만나는 순간 집계한 값을 운반 단위에 저장
- B구간 스캔하다가 C 만나는 순간 집계한 운반 단위에 저장
- C구간 스캔하다가 D 만나는 순간 집계한 운반
단위 저장 Array Size가 3이므로 A, B, C 대한 집계 결과를 클라이언트에 전송하고, 다음 Fetch Call이 올때까지 기다린다. - 클라이언트로 부터 Fetch Call 오면 1~3 과정을 반복한다.
이렇게 인덱스를 이용해 Nosort 방식으로 Group by 처리하면 부분범위 처리가 가능하다.
