친절한 SQL 튜닝 - 2.3 인덱스 확장기능 사용법

---

2.3.1 Index Range Scan

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

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

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

그러나 인덱스 잘타니까 성능도 OK 는 아니다!

성능은 인덱스 스캔 범위, 테이블 액세스 횟수등을 얼마나 줄일 수 있는가? 에 따라 달라진다.

---

2.3.2 Index Full Scan

Index Full Scan은 수직적 탐색 없이 인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색한다.

데이터 검색을 위한 최적의 인덱스가 없을 때 차선으로 선택한다. (최적의 인덱스가 존재한다면 Index Range Scan)

index (ename, sal)

select * from emp
where sal > 2000
order by ename;
// 이럴 때, index full scan 발생

인덱스 선두컬럼인 ename이 where 조건절에 없기 때문에 index range scan 불가능하다.

두번째에 있는 sal로 index full scan이 일어난다.

---

2.3.3 Index Unique Scan

"정확히 딱 해당되는 값을 찾을 때"

Index Unique Scan은 수직적 탐색으로만 데이터를 찾는 스캔 방식이다.

Unique Index를 = 조건을 탐색하는 경우에 작동한다.

(Unique Index가 존재하는 컬럼은 중복값이 입력되지 않게 dbms가 데이터 정합성을 관리한다.)

=> 해당 인덱스 키 컬럼을 모두 '=' 조건으로 검색할 때는 데이터를 한 건 찾는 순간 더 이상 탐색할 필요가 없다. (중복되는 값 없으니깐)

Unique Index라도 empno >= 7888 처럼 범위검색으로 검색하게되면 Index Range Scan으로 처리된다.

수직적 탐색으로는 조건에 해당하는 레코드를 모두 찾을 수 없기 때문이다.

PK인덱스가 [주문일자+고객ID+상품ID]이지만, 주문일자와 고객ID 두 개만가지고 검색하면 Index Range Scan

---

2.3.4 Index Skip Scan

인덱스 선두 컬럼을 조건절에서 사용하지 않으면, 옵티마이저는 기본적으로 Table Full Scan을 선택한다.

Table Full Scan 보다 IO를 줄일 수있거나 정렬된 결과를 쉽게 얻을 수 있다면, Index Full Scan 사용한다.

Index Skip Scan

• 인덱스 선두 컬럼의 distinct value개수가 적고 후행 컬럼의 distinct value 개수가 많을 경우 유용

(distinct value가 적다는것은 '남', '여'처럼 보기가 별로 없다는 것)

 

create index idx1 on emp(sex,salary); 

select * 
from emp 
where sex='남' and salary between 2000 and 4000; 

select /*+ index_ss(idx1)*/ * 
from emp 
where salary between 2000 and 4000;

• 첫번째 쿼리는 index range scan
• 두번째 쿼리는 힌트를 사용하여 index skip scan을 유도한 경우이다.

index skip scan은 루트 또는 브랜치 블록에서 읽은 컬럼 값 정보를 이용해 조건절에 부합하는 레코드를 포함할 '가능성이 있는' 리프 블록만 골라서 액세스하는 스캔 방식이다.

(예측한다? 라는 표현이 잘 맞는듯?)

 

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

Distict Value 개수가 적은 선두 컬럼이 조건절에 없고, 후행 컬럼이 distinct value 개수가 많을 때 효과적이다.

그러나, 인덱스 선두 컬럼이 없을 때만 index skip scan이 작동하는 것은 아니다.

 

 

• ex1) 선두컬럼에 대한 조건절(업종유형코드)은 있지만 중간 컬럼(업종코드)에 대한 조건절이 없는 경우

select /*+ INDEX_SS(A index1_pk) */
        기준일자, 업종코드, 체결건수, 체결수량, 거래대금
from 일별업종거래 A
where 업종유형코드='01'
and 기준일자 between '20080501' and '20080531'

index1_pk : [업종유형코드 + 업종코드 + 기준일자]

 

index range scan의 경우 업종유형코드='01'인 인덱스 구간을 모두 스캔`

index skip scan을 사용한다면 업종유형코드='01'인 구간에서 기준일자가 해당 범위에 있는 레코드를 포함할 가능성이 있는 리프 블록만 골라서 액세스

 

 

 

• ex2) distinct value가 적은 두개의 선두컬럼이 모두 조건절에 없는 경우

select /*+ INDEX_SS(A index1_pk) */
        기준일자, 업종코드, 체결건수, 체결수량, 거래대금
from 일별업종거래 A
where 기준일자 between '20080501' and '20080531'

 

 

 

 

• ex3) between , like 와 같은 범위검색 조건일 때도 Index Skip Scan 사용 가능

인덱스 : 기준일자 +업종유형코드

select /*+ INDEX_SS(A index1_pk) */
        기준일자, 업종코드, 체결건수, 체결수량, 거래대금
from 일별업종거래 A
where 기준일자 between '20080501' and '20080531' 
and 업종유형코드='01'

 

만약 index range scan을 사용하는 경우 between 조건을 만족하는 범위를 모두 스캔함

index skip scan을 사용하는 경우 between 조건을 만족하는 인덱스 구간에서 업종유형코드='01'인 에코드를 '포함할 가능성이 있는 리프 블록만' 골라서 액세스 가능

---

2.3.5 Index Fast Full Scan

Index Fast Full Scan 이 Index Full Scan 보다 빠른이유

: 논리적인 인덱스 트리 구조를 무시하고 인덱스세그먼트 전체를 Multiblock IO 방식으로 스캔하기 때문이다,

Multiblock IO 방식 사용

인덱스가 파티션 돼 있지 않더라도 병렬 쿼리가 가능

병렬 쿼리시에는 Direct Path IO 방식을 사용

 

Index  Full Scan	Index  Fast Full Scan
인덱스를 구조에 따라 스캔	세그먼트 전체를 스캔결과집합 순서 보장 안됨Multiblock IO병렬스캔 가능인덱스에 포함된 컬럼으로만 조회할 때 사용 가능
결과집합 순서 보장	결과집합 순서 보장 안됨
Single Block IO	Multiblock IO
(파티션 돼 있지 않다면) 병렬 스캔 불가	병렬스캔 가능
인덱스에 포함되지 않은 컬럼 조회 시에도 사용 가능	인덱스에 포함된 컬럼으로만 조회할 때 사용 가능

---

2.3.6 Index Range Scan Descending

Index Range Scan과 기본적으로 동일한 스캔 방식이다.

인덱스를 뒤에서부터 앞쪽으로 스캔하기 때문에 내림차순으로 정렬된 결과집합을 얻을 수 있다.