Note 002 # 인덱스 스캔방식

Note 002_01     # 오라클에서 사용가능한 스캔방식

오라클에는 다음과 같은 스캔방식이 존재한다.

• TABLE FULL SCAN
• INDEX UNIQUE SCAN
• INDEX RANGE SCAN
• INDEX RANGE SCAN DESCENDING
• INDEX FULL SCAN
• FAST INDEX FULL SCAN
• INDEX SKIP SCAN

해당 방식으로 유도하는 힌트는

링크된 오라클 문서를 참조하기 바란다.

Note 002_02     # 각 상황에 유리한 스캔방식

다음과 같은 상황에는 이러한 스캔방식이 유리하다.

필터링 결과 테이블 크기	소량	대량	극대량	RANGE 스캔 불가 소량
작음	INDEX RANGE	X	X	FAST INDEX FULL
큼	INDEX RANGE	FAST INDEX FULL	X	FAST INDEX FULL
매우 큼	INDEX RANGE	파티셔닝 고려	파티셔닝 고려	파티셔닝 고려

랜덤 액세스가 많이 발생하여 FAST INDEX FULL SCAN이 성능상 불리할 때는

TABLE FULL SCAN으로 유도한다.

Note 002_03     # FAST INDEX FULL SCAN의 특징

• 세그먼트 전체를 Multiblock I/O 으로 스캔.
• 다른 스캔방식과 다르게 결과집합 정렬보장 안됨.
• 인덱스가 파티션되어있지 않더라도 병렬스캔 가능.
• 인덱스에 포함된 컬럼으로만 조회할 때만 사용가능.

Note 002_04     # INDEX SKIP SCAN의 특징

• 선행 컬럼들의 변별력이 매우 낮을 때 효과적
• 선행 컬럼들이 누락되었을 때에도 사용 가능
• IN-LIST 연산자로 대체가능.  (하지만 원리는 다름.)

Plus Note 002_01     # 각 상황에 유리한 스캔방식     # 손익분기점

타겟 컬럼들이 인덱스에 전부 포함되어있지 않다면

인덱스에 없는 컬럼을 조회하기 위해 테이블 랜덤 액세스가 발생한다.

이 테이블 랜덤 액세스는 비용이 굉장히 비싼편이라

Index Range Scan을 사용하더라도 테이블 랜덤 액세스가 대량 발생한다면

오히려 Table Full Scan보다 성능이 불리할 수 있다.

Index Range Scan 방식이 Table Full Scan보다 불리해지는 지점을

인덱스의 손익분기점이라고 하며 시각적으로는 아래와 같다.

위 그래프를 분석하면, 대략 500건 이상의 랜덤액세스가 발생할 경우

인덱스를 사용하는 것 보다 오히려 Full Table Scan이 유리하다는 것을 알 수 있다.

손익분기점은 다음과 같은 요인에 의하여 변동될 수 있다.

• 해당 인덱스의 클러스터링 팩터(CF)

다음은 클러스터링 팩터 상황이 좋아 손익분기점이 굉장히 좋은 상태의 그래프이다.

클러스터링 팩터가 좋다는 것은

해당 테이블의 정렬순서와 해당 인덱스의 정렬순서가 비슷하다는 것을 의미한다.

둘의 정렬순서가 비슷하면 버퍼 Pinning효과가 발생하여

같은량의 테이블 랜덤 액세스가 발생하더라도 물리적 IO가 감소하기 때문에

손익분기점이 상당히 높은 지점에 위치하게 되는 것 이다.

Plus Note 002_02     # Fast Index Full Scan의 특징     # 정렬집합이 보장되지 않음

Fast Index Full Scan은 인덱스 구조를 무시하고 Multiblock-IO를 사용하여 블럭을 읽는다.

따라서 다른 인덱스 스캔방식과는 다르게 결과집합이 정렬되지 않는다.

다음은 각각 Range 스캔과 Fast Full 스캔의 결과집합이다.

-- Index Range Scan
-- 인덱스 X_LINEITEM_02 : [L_PARTKEY + L_SUPPKEY]
SELECT L_PARTKEY, L_SUPPKEY
FROM   LINEITEM
WHERE  L_PARTKEY < 10
AND    L_SUPPKEY < 10
GROUP BY L_PARTKEY, L_SUPPKEY;

 L_PARTKEY  L_SUPPKEY
---------- ----------
         1          2
         2          3
         3          4
         4          5
         5          6
         6          7
         7          8
         8          9

-- Fast Index Full Scan
-- 인덱스 X_LINEITEM_02 : [L_PARTKEY + L_SUPPKEY]
SELECT /*+ INDEX_FFS(LINEITEM X_LINEITEM_02)*/ L_PARTKEY, L_SUPPKEY
FROM   LINEITEM
WHERE  L_PARTKEY < 10
AND    L_SUPPKEY < 10
GROUP BY L_PARTKEY, L_SUPPKEY;

 L_PARTKEY  L_SUPPKEY
---------- ----------
         8          9
         4          5
         7          8
         2          3
         5          6
         1          2
         3          4
         6          7

정렬이 필요한 상황이라면 ORDER BY 절을 추가적으로 기술해주어야 한다.

추가적으로 위의 성질을 이용하여 Range 또는 Index Full 스캔에서 정렬연산을 생략할 수 있다. 

자세한 내용은 추후 또 다른 노트에서 설명한다.

Plus Note 002_03     # Fast Index Full Scan의 특징     # 인덱스에 포함된 컬럼으로만 조회할 때만 사용가능

다음 쿼리와 실행계획을 살펴보자.

FFS 힌트를 사용하였지만 인덱스에 포함되지 않은 컬럼을 조회함으로써

실행계획에서 힌트가 무시되었음을 주목하기 바란다.

-- Fast Index Full Scan
-- 인덱스 X_LINEITEM_02 : [L_PARTKEY + L_SUPPKEY]
SELECT /*+ INDEX_FFS(LINEITEM X_LINEITEM_02)*/ L_PARTKEY, L_SUPPKEY, L_ORDERKEY
FROM   LINEITEM
WHERE  L_PARTKEY < 10
AND    L_SUPPKEY < 10;

-----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |     1 |    15 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| LINEITEM      |     1 |    15 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | X_LINEITEM_02 |     1 |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("L_PARTKEY"<10 AND "L_SUPPKEY"<10)
       filter("L_SUPPKEY"<10)

Plus Note 002_04     # Index Skip Scan의 특징     # 선행 컬럼들의 변별력이 낮고, 누락되었을 때 효과적

다음은 인덱스가 [판매월 + 판매구분] 으로 이루어져있고,

판매구분이 A인 레코드를 조회할 때 Index Skip Scan의 스캔범위를 나타낸 그림이다.

쉽게 설명하자면 판매월을 각각 GROUP화 하여 각각의 그룹에서

판매구분이 A인 레코드를 Range Scan하는 것 이다.

어차피 판매월 전체를 그룹화하기 때문에 

판매월이 조건절에서 누락되어도 상관없다.

다만 GROUP 개수가 너무 많거나 B처럼 스캔되는 레코드량이 많다면

Index Full Scan에 가까워지기 때문에 데이터 상황에 맞추어 사용해야한다.

Plus Note 002_05     # Index Skip Scan의 특징     # In-List 연산자로 대체가능.

그룹화 개수가 적을때에는 직접 In-List 연산자로 도메인을 지정해주면,

Skip Scan에 비해 성능이 더욱 향상된다.

인덱스 스킵 스캔은 항상 내부적인 비효율을 가지고 있기 때문이다.

In-List 연산자는 그러한 비효율을 제거할 수 있는 대안이다.

이러한 비효율은 인덱스 스캔범위 원리에서 시작되며

이에 관한 자세한 내용은 추후 또 다른 노트에서 설명한다.

-- In-List 연산자로 대체
-- 인덱스 [판매월 + 판매구분]
SELECT 판매월, 판매구분
FROM   판매
WHERE  판매월 IN (200801, 200802, 200803, ...)
AND    판매구분 = 'A';

다만 In-List로 대체할 경우에는

추후 또 다른 GROUP이 조회목록에 추가되지 않는 경우에만 적용해야 한다.