인덱스 스캔 방식
1. Index Range Scan
- 가장 일반적인 인덱스 스캔 방식이다.
- root block -> branch block -> leaf block 순으로 수직 탐색한 뒤, 필요한 leaf block 구간만 스캔한다.
- 스캔 범위, 즉 Range를 얼마나 줄일 수 있느냐에 따라 성능이 좌우된다.
- 일반적으로 인덱스 선두 컬럼이 조건절에 사용되어야 효율적으로 동작한다.
- 결과는 인덱스 컬럼 순으로 정렬된 상태로 읽힌다. 따라서 조건과 정렬 방향이 맞으면 별도의 정렬 작업 없이 ORDER BY를 만족할 수 있으며, MIN/MAX 추출에도 유리하다.
2. Index Range Scan Descending
- Index Range Scan과 기본 동작은 동일하지만, leaf block을 역방향으로 스캔한다.
- 내림차순 정렬 결과가 필요하거나 MAX 값을 빠르게 찾을 때 유용할 수 있다.
3. Index Full Scan
- 먼저 수직 탐색을 수행한다. 이는 가장 왼쪽의 leaf block을 찾기 위해서다.
- 가장 왼쪽의 leaf block을 찾고 나면, 인덱스 leaf block을 처음부터 끝까지 수평적으로 스캔한다.
- 인덱스 선두 컬럼이 조건절에 없더라도, 인덱스만 읽어서 조건 필터링이나 정렬을 처리하는 것이 유리하다고 판단되면 Index Full Scan이 고려될 수 있다.
- 인덱스 순서를 유지해서 읽기 때문에 ORDER BY를 피하는 데 도움이 될 수 있지만, 인덱스 전체를 읽는다는 점에서는 비용이 커질 수 있다.
4. Index Fast Full Scan
- Index Full Scan보다 빠를 수 있는 이유는 인덱스의 논리적 순서를 따라 읽지 않고, 인덱스 segment 전체를 multiblock read 방식으로 읽을 수 있기 때문이다.
- 인덱스만으로 필요한 컬럼을 모두 얻을 수 있을 때 주로 고려된다.
- 다만 인덱스 키 순서를 보장하지 않으므로, 정렬 결과가 필요한 경우에는 별도의 정렬 작업이 필요할 수 있다.
5. Index Unique Scan
- Unique 인덱스 또는 Primary Key 인덱스의 모든 컬럼에 대해 = 조건을 사용하는 경우에 가능하다.
- 단, = 조건이 아닌 BETWEEN 같은 범위 조건이 들어오면 Unique 인덱스를 사용하더라도 Index Range Scan으로 동작한다.
- Unique 결합 인덱스를 사용하는 경우, 조건절에 모든 인덱스 컬럼을 사용하지 않으면 Index Range Scan으로 동작한다. 추출 데이터가 단 1건이라는 보장이 없기 때문이다.
- Unique 인덱스는 조건을 모두 만족하면 결과가 최대 1건임을 보장할 수 있으므로, 필요한 위치를 찾은 뒤 추가 범위 스캔을 거의 하지 않는다.
6. Index Skip Scan
- Oracle 9i부터 가능한 방식이다.
- 인덱스 선두 컬럼이 조건절에 빠진 경우에도, 선두 컬럼의 distinct 수가 적고 후행 컬럼의 distinct 수가 많을 때 유용하다.
- 개념적으로는 선두 컬럼의 값별로 작은 Range Scan을 여러 번 수행하는 방식에 가깝다.
- 결국 Full Scan보다 I/O가 적다고 옵티마이저가 판단해야 선택된다.
인덱스 스캔이 어려운 경우
- 인덱스 컬럼에 변형이 가해지는 경우. 예를 들어 컬럼에 함수를 적용하거나, 숫자형 컬럼에 연산을 수행하면 일반 인덱스를 그대로 사용하기 어려울 수 있다.
- NULL 또는 NOT NULL 조건을 사용하는 경우. 특히 일반 B-tree 인덱스는 모든 인덱스 컬럼이 NULL인 엔트리를 저장하지 않으므로 주의가 필요하다.
- NOT, <>, != 같은 부정 비교를 사용하는 경우. 조건의 선택도가 낮아지기 쉬워 인덱스보다 Full Scan이 선택될 수 있다.
- CBO가 통계 정보와 비용을 기준으로 Full Scan이 더 유리하다고 판단하는 경우.
다만 위 조건이라고 해서 항상 인덱스를 사용하지 않는 것은 아니다. 함수 기반 인덱스가 있거나, 조건절을 인덱스 컬럼 형태에 맞게 바꾸거나, 선택도가 충분히 높다면 인덱스 스캔이 선택될 수 있다. 따라서 실제 SQL에서는 실행 계획을 확인하는 것이 가장 확실하다.
실행 계획 확인
플랜 테이블은 다음 스크립트를 실행해서 생성할 수 있다.
$ORACLE_HOME/rdbms/admin/utlxplan.sql
실행 계획은 다음과 같이 확인한다.
EXPLAIN PLAN FOR [SQL문]
실행 후에는 PLAN_TABLE을 조회하거나 DBMS_XPLAN.DISPLAY를 사용해 실제로 어떤 인덱스 스캔 방식이 선택되었는지 확인한다. 테스트할 때는 조건절을 조금씩 바꿔 보면서 Index Range Scan, Index Full Scan, Index Fast Full Scan의 차이를 비교하면 이해하기 쉽다.