SQL튜닝이론 - 다양한 인덱스스캔방법 (INDEX UNIQUE SCAN, INDEX SKIP SCAN, INDEX FAST FULL SCAN)

SQL튜닝이론 - 다양한 인덱스스캔방법 (INDEX UNIQUE SCAN, INDEX SKIP SCAN, INDEX FAST FULL SCAN)

 

INDEX UNIQUE SCAN

 

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

 

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    20 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    20 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

 

 

Unique 인덱스가 있는 컬럼은 중복값이 입력되지 않도록 DBMS가 데이터 정합성을 관리합니다. 따라서 해당 컬럼을 = 조건으로 검색하면 데이터를 한 건 찾는 순간 더이상 찾을 필요가 없습니다. 단, UNIQUE 인덱스라고 해도 범위 조건으로 검색하면 INDEX RANGE SCAN으로 처리됩니다. 또한, UNIQUE 결합 인덱스에 대해 하위 컬럼을 조건절로 검색하면 INDEX RANGE SCAN이 나타납니다. [주문일자 + 고객ID + 상품ID] 로 UNIQUE 인덱스를 구성하고 주문일자와 고객 ID로만 검색하는 경우가 이에 해당합니다.

 

INDEX SKIP SCAN

 

인덱스 선두 컬럼을 조건절에 사용하지 않으면 옵티마이저는 기본적으로 TABLE FULL SCAN을 선택합니다. 다만 인덱스를 풀 스캔했을때 테이블 풀 스캔에 비해 I/O를 줄일 수 있거나 정렬된 결과를 쉽게 얻을 수 있다면 INDEX FULL SCNA을 사용합니다.

 

인덱스 선두 컬럼이 조건절에 없을때 인덱스를 활용하는 세로운 스캔방식이 INDEX SKIP SCAN 입니다. 이 스캔 방식은 조건절에 빠진 INDEX 선두 컬럼의 DISTINCT VALUE 의 수가 적고, 후행 컬럼의 그것이 많은 경우 적합합니다. [성별 + 고개번호] 로 구성된 인덱스가 이에 해당합니다.

 

create index emp_deptno_sal_idx on emp (deptno, sal);

EXPLAIN plan FOR
select /*+index_ss(emp emp_deptno_sal_idx)*/ *
from emp
where sal between 2000 and 4000;

SELECT *
FROM TABLE(dbms_xplan.display);


----------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                    |     4 |   348 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP                |     4 |   348 |     2   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN                   | EMP_DEPTNO_SAL_IDX |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("SAL">=2000 AND "SAL"<=4000)
       filter("SAL">=2000 AND "SAL"<=4000)

 

INDEX SKIP SCAN은 루트 또는 브랜치 블록에서 읽은 컬럼 값 정보를 이용해 조건절에 부합하는 레코드를 포함할 가능성이 있는 리프블록만 골라 엑세스하는 방식입니다. 성별이 '남' 이고 연봉이 800 이상 1500 이하인 레코드가 담긴 블록은 skip 됩니다. 마찬가지로 남&5000 이상 블록과 이후 블록은 모두 SKIP 대상입니다.

Index Skip Scan 의 작동조건

 

인덱스 선두 컬럼이 조건절에 없을때만 작동하는 것은 아닙니다. 특정인덱스 구성에서 선두컬럼은 조건절에 있고 중간 컬림은 없는 경우에도 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인 구간에서 기준일자가 조건에 해당할 가능성이 있는 블록만 골라서 액세스 할 수 있습니다.

아래와 같이 DISTINCT VALUE 가 적은 두 개의 선두 컬럼이 조건절에 없는 경우에도 유용하게 사용할 수 있습니다.

 

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

 

선두컬럼이 부등호, BETWEEN, LIKE 같은 범위 검색 조건일 때도 사용할 수 있습니다.

 

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

 

기준일자가 BETWEEN 조건을 만족하는 인덱스 구간에서 업종유형코드가 01인 레코드가 있을 가능성이 있는 블록만 골라서 엑세스할 수 있습니다.

 

하지만 SKIP SCAN 방식은 최선책이 아닙니다. 인덱스는 기본적으로 최적의 INDEX RANGE SCAN을 목표로 설계해야하며, 수행횟수가 적은 SQL을 위해 인덱스를 추가하는 것이 비효율적일때 SKIP SCAN 방식을 차선으로 활용해야합니다.

 

Index Fast Full Scan

 

인덱스 트리의 논리적인 구조를 무시하고 인덱스 세그먼트 전체를 Multiblock I/O 방식으로 빠르게 스캔합니다. 관련힌트는 index_ffs와 no_index_ffs 입니다.

 

디스크로부터 대량의 인덱스 블록을 읽어야 할 때 큰 효과르 발휘합니다. 단, 조회결과가 인덱스 키의 순서대로 정렬되지 않습니다. 쿼리에 사용한 컬럼이 모두 인덱스에 포함돼 있을 때만 사용가능합니다. 인덱스가 파티션 돼 있지 않더라도 병렬 처리가 가능한 점도 중요한 특징입니다.