인덱스 확장기능 사용법

이재호·2023년 3월 8일

Database Index 데이터베이스 인덱스

참고1

2.3.1 Index Range Scan

인덱스 루트에서 리프 블록까지 수직적으로 탐색한 후에, 필요한 범위만 스캔하는 방법

선두컬럼을 가공하지 않은 상태로 조건절에 사용하면 Index Range Scan은 무조건 가능하다.

2.3.2 Index Full Scan

수직적 탐색없이 인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색하는 방식이다.

데이터검색을 위한 최적의 인덱스가 없을 때 차선으로 선택된다.

create index emp_ename_sal_idx on emp (ename, sal);

select * from emp
where sal > 2000
order by ename;

위 예제에서 선두 컬럼인 ename이 조건절에 없으므로 Index Range Scan은 불가능하지만 sal컬럼이 인덱스에 있으므로 Index Full Scan을 사용하게된다.
옵티마이저는 선두컬럼이 조건절에 없으면, 먼저 Table Full Scan을 고려한다. 하지만 대용량 테이블이어서 Table Full Scan에 따른 부담이 크다면 인덱스 활용을 고려하는것이 좋다.
데이터 저장공간은 컬럼길이 X 레코드 수에 의해 결정되므로, 인덱스가 차지하는 면적은 테이블보다 훨씬 적다.

인덱스를 이용한 소트연산 생략

인덱스를 Full Scan하면 Range Scan과 마찬가지로 결과집합이 인덱스 컬럼 순으로 정렬된다. 따라서 Sort Order By 연산을 생략할 목적으로 사용할 수도 있다.

select /*+ first_rows */ *
from emp
where sal > 1000
order by ename;

대부분 사원이 sal > 1000을 만족하는 상황에서 Index Full Scan을 선택하면, 거의 모든 레코드에 대해 테이블 액세스가 발생하므로 Table Full Scan보다 오히려 불리하다.
first_rows힌트를 사용한 이유는 부분범위 처리가 가능한 상황에서 극적인 성능 개선 효과를 가져다 준다.
사용자가 처음 의도와 달리 fetch를 멈추지 않고 데이터를 끝까지 읽는다면, Table Full Scan 보다 훨씬 더 많은 I/O를 일으키고 결과적으로 수행속도도 느려진다.

count함수를 사용할때도 Index Full Scan을 사용한다.
Min/Max를 사용할때 인덱스가 정렬되어 있으므로 한 개의 블록을 읽어 결과를 얻을 수 있다.
하지만 Full Table Scan은 multiblock-read가 가능하지만 index full scan은 singleblock-read를 하기 때문에 오히려 속도가 저하될 수 있는 가능성이 있다.

2.3.3 Index Unique Scan

수직적 탐색만으로 데이터를 찾는 스캔 방식으로, Unique 인덱스를 '=' 조건으로 탐색하는 경우에 작동한다.

Unique 인덱스가 존재하는 컬럼은 중복 값이 입력되지 않게 DBMS가 데이터 정합성을 관리해준다. 따라서 '='조건으로 탐색 시 데이터를 한 건 찾는 순간 더 이상 탐색할 필요가 없다.
Unique 인덱스라고 해도 범위검색 조건으로 검색할때는 Index Range Scan으로 처리된다.
Unique 결합 인덱스에 대해 일부 컬럼만으로 검색할 때도 Index Range Scan이 나타난다.

2.3.4 Index Skip Scan

오라클에서는 인덱스 선두 컬럼이 조건절에 없어도 인덱스를 활용하는 새로운 스캔방식을 선보였는데, Index Skip Scan이 바로 그것이다. 조건절에 빠진 인덱스 선두 컬럼의 Distinct Value 개수가 적고 후행 컬럼의 Distinct Value 개수가 많을 때 유용하다.

select * from 사원 where 성별 = '남' and 연봉 between 2000 and 4000

먼저 성별='남'이면서 연봉 >= 2000인 첫 번째 레코드를 찾는다.
리프 블록에서 성별='남'이면서 연봉 >= 2000인 첫 번째 레코드를 만났다면, 거기서부터 차례로 스캔하다가 성별='남'이면서 연봉 > 4000인 첫 번째 레코드를 만나는 순간 스캔을 멈춘다.

select /*+ index_ss(사원 사원_IDX) */ *
from 사원
where 연봉 between 2000 and 4000;

Index Skip Scan은 루트 또는 브랜치 블록에서 얻은 컬럼 값 정보를 이용해 조건절에 부합하는 레코드를 포함할 가능성이 있는 리프블록만 골라서 액세스하는 스캔 방식이다.
남 & 800 이하인 레코드를 담고 있어서 이 블록은 액세스 하지 않아도 될 것 같지만, '남'보다 작은 성별값이 혹시 존재한다면, 액세스 해야만한다.
두번째 레코드가 가리키는 블록은 남&800 이상이면서, 남&1500이하인 레코드를 담고 있다. 존재할 가능성이 없으므로 Skip한다.
세번째는 액세스한다.
네번째는 Skip한다.
여섯번째는 남&10000 이상이므로 연봉구간을 초과하지만, 여&3000이하인 데이터를 담고 있기 때문에 액세스한다.
마지막으로 열 번째 리프블록도 마찬가지로 '여'보다 큰 성별 값이 존재할 수 있으니 액세스 해야만한다.

Index Skip Scan이 작동하기 위한 조건

인덱스 선두 컬럼이 없을때만 작동하는 것은 아니다.

일별업종별거래_PK : 업종유형코드 + 업종코드 + 기준일자

SELECT /*+ INDEX_SS(A 일별업종거래_PK) */
기준일자, 업종코드, 체결건수, 체결수량, 거래대금
FROM 일별업종별거래 A
WHERE 업종유형코드='01'
AND 기준일자 BETWEEN '20080501' AND '20080531'

만약 Index Range Scan을 사용한다면 업종유형코드 ='01'인 인덱스 구간을 모두 스캔해야한다. Index Skip Scan을 사용한다면, 업종유형코드 ='01'인 구간에서 기준일자의 조건을 포함할 가능성이 있는 리프 블록만 골라서 액세스할 수 있다.
선두 컬럼이 부등호, BETWEEN, LIKE같은 범위검색 조건일 때도 Index Skip Scan을 사용할 수 있다.