[SQL] 인덱스 스캔 방식 - Index Range, Index Full, Index Unique, Index Skip

인덱스 탐색은 수평적 탐색과 수직적 탐색으로 나눠진다.
수평적 탐색은 인덱스 리프 블록에 저장된 레코드끼리 연결된 순서에 따라 좌->우 또는 우->좌로 탐색하는 과정이다.
수직적 탐색은 수평적 탐색을 위한 시작 지점을 찾는 과정으로, 루트에서 리프 블록까지 아래쪽으로 진행된다.

 

1. Index Range Scan

Index Range Scan

Index Range Scan은 인덱스 루트 블록에서 리프 블록까지 수직적으로 탐색한 후에 리프 블록을 필요한 범위만 스캔하는 방식이다.

B*Tree 인덱스의 가장 일반적이고 정상적 형태의 액세스 방식이다.

 

set autotrace traceonly exp
select * from emp where deptno = 20;

인덱스가 EMP_X01(deptno)인 emp 테이블의 실행계획은 다음과 같다.

★ Index Range Scan이 가능하게 하려면 인덱스를 구성하는 선두 칼럼이 조건절에 사용되어야 하며, Index Range Scan 과정을 거쳐 생성된 결과 집합은 인덱스 칼럼 순으로 정렬된다.

 

 

2. Index Full Scan

Index Full Scan

 

Index Full Scan은 수직적 탐색없이 인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색하는 방식으로, 주로 데이터 검색을 위한 최적의 인덱스가 없을 때 차선으로 선택된다.

위에서 Index Range Scan을 사용하려면 인덱스를 구성하는 선두 칼럼이 조건절에 사용된다고 했는데, 이를 어기고 인덱스를 사용하도록 강제한다면 Index Full Scan 방식으로 처리된다.

set autotrace traceonly exp

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

인덱스가 EMP_IDX(deptno, sal)인 emp 테이블의 실행계획은 다음과 같다.

인덱스를 구성하는 선두 칼럼(deptno) 가 조건절에 없어서 발생한 스캔

수직적 탐색 없이 수평적으로만 탐색한다고 한것은 개념적인 설명일뿐 실제로 수직적 탐색이 먼저 일어난다. 루트 블록과 브랜치 블록을 거치지 않고서는 가장 왼쪽의 첫번째 리프 블록으로 찾아갈 방법이 없기 때문이다.

 

인덱스 선두 칼럼이 조건절에 없으면 옵티마이저는 우선적으로 Table Full Scan을 고려하며, Table Full Scan의 부담이 크다면 인덱스를 활용하는 방법을 사용한다.

인덱스 스캔 단계에서 대부분 레코드를 필터링하고 일부에 대해서만 테이블 액세스가 발생하는 경우는 Table Full Scan보다 Index Full Scan을 고려할 수 있다.

 

★ Index Full Scan은 Index Range Scan처럼 결과집합이 인덱스 칼럼 순으로 정렬된다.

 

 

3. Index Unique Scan

Index Unique Scan

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

set autotrace traceonly exp
select empno, ename from emp where empno = 7788;

Unique 인덱스가 PK_EMP(empno)인 emp 테이블의 실행계획은 다음과 같다.

 

 

4. Index Skip Scan

Index Skip Scan

앞서 말했듯이, 인덱스 선두 칼럼이 조건절로 사용되지 않으면 옵티마이저는 기본적으로 Table Full Scan / Index Full Scan 방식을 사용한다.

 

Oracle은 인덱스 선두 칼럼이 조건절에 빠져도 인덱스를 활용하는 새로운 스캔방식인 Index Skip Scan을 선보였다.

 

Index Skip Scan 는 루트 또는 브랜치 블록에서 읽은 칼럼 값 정보를 이용해 조건에 부합하는 레코드를 포함한 '가능성이 있는' 하위 블록만 골라서 액세스하는 방식이다.

 

성별, 분류코드 등 으로 결합 인덱스를 구성할 경우를 예로 들 수 있다.

// (1) Index Skip Scan
select * from 사원 where 연봉 between 2000 and 4000;

// (2) In-List 사용
select * from 사원
where 연봉 between 2000 and 4000
and 성별 in ('남' , '여');

 

 (1) 에서는 Index Skip Scan이 사용되고 (2) 와 같이 In-List를 명시하면 Index Range Scan이 사용된다.

★ (2)로 튜닝 효과를 발휘하려면 In-List로 제공하는 값의 종류가 적어야 한다.