[Oracle] 오라클 인덱스 스캔 방식

Index Range Scan

- B*Tree 인덱스의 가장 일반적이고 정상적인 형태의 액세스 방식(수직적 탐색 후 필요한 범위만 수평적 탐색)

- 항상 빠른 속도를 보장하진 않음

- 인덱스 스캔 범위를 얼만큼 줄일 수 있느냐, 테이블 액세스 횟수를 얼만큼 줄일 수 있느냐가 관건

   => 인덱스 설계와 SQL 튜닝의 핵심 원리

- 인덱스를 구성하는 선두 컬럼이 조건절에 사용되어야 함

Index Full Scan

- 수직적 탐색없이 인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색하는 방식

- 최적의 인덱스가 없을 때 차선책(인덱스 선두 컬럼이 조건절에 없을 경우)

- 최종 결과값이 적을때 Table Full Scan보다 Index Full Scan이 훨씬 효율적

Index Unique Scan

- 수직적 탐색만으로 데이터를 찾는 스캔 방식

- Unique Index를 통해 '=' 조건으로 탐색하는 경우

* Unique Index를 이용했지만 Index Range Scan이 나타나는 경우

- Unique Index를 통해 범위 조건(between, 부등호, like)으로 검색하는 경우

- 결합 형태인 Unique Index의 일부 컬럼만으로(선두 컬럼 포함) 검색하는 경우

Index Skip Scan

- 인덱스가 세 개의 컬럼으로 구성되어 있는데 중간 컬럼에 대한 조건절만 누락된 경우나 Distinct Value가 적은 두 개의 선두 컬럼이 모두 누락된 경우

- 선두 컬럼이 범위 조건(between, 부등호, like) 검색인 경우

- 조회 조건에 인덱스 선두 컬럼이 없는데 선두 컬럼의 Distinct Value 개수가 적은 경우

ex) SELECT *

        FROM TUSER

     WHERE HOMETOWN = '서울';

     ======= Scan 과정 =======

	INDEX
선두 컬럼의 가장 작은 값 확인     →	남	광주
pass	남	대전
'남'이면서 '서울'인 레코드 탐색    →	남	서울
'남'이면서 '서울'인 레코드 탐색    →	남	서울
pass	남	제주
pass	여	강릉
pass	여	부산
'여'이면서 '서울'인 레코드 탐색   →	여	서울
선두 컬럼의 가장 큰 값 확인      →	여	울산

=> 가능성 있는 리프 블록만 골라서 액세스하는 방식

※ 리프 블록의 정보만으로는 어디로 점프해야할지 알 수가 없으므로 상위 블록 버퍼를 Pinning 해두는 방식을 사용

Index Fast Full Scan

- Index Full Scan보다 빠름

- Index를 논리적 구조 순으로 읽지 않고 물리적으로 디스크에 저장된 순서대로 읽음(Segment 전체를 Scan)

 (논리적으로 1번 Leaf와 2번 Leaf는 짝꿍이지만 물리적으로는 다른 Extent에 저장되어 있을 수 있음)

- Multiblock I/O

- 결과 집합이 Sorting된 상태가 아닐 수 있음

- 쿼리에 사용되는 모든 컬럼이 인덱스 컬럼에 포함돼 있을 때만 사용 가능

- 병렬스캔 가능

튜닝 사례

SELECT *

  FROM 공급업체

WHERE 업체명 LIKE '%네트웍스%';

-- 결과 건수가 많지 않다고 가정

-- 공급업체의 업체명 컬럼에 생성한 인덱스는 사용빈도가 높지 않아 가끔 조회될 때마다 디스크 I/O가 많이 발생

▼

SELECT /*+ ordered use_nl(B) no_merge(B) rowid(B) */ B.*

  FROM ( SELECT /*+ index_ffs(공급업체 공급업체_X01) */ ROWID RID

                FROM 공급업체

  WHERE INSTR(업체명, '네트웍스') > 0 ) A, 공급업체 B

WHERE B.ROWID = A.RID;

-- Index Fast Full Scan은 모든 컬럼이 인덱스에 포함돼 있을 때만 사용 가능

-- 일단 해당 레코드의 ROWID만 Index Fast Full Scan으로 빠르게 get

-- 인덱스로부터 얻은 ROWID로 테이블 액세스

인덱스를 이용한 소트 연산 대체

SELECT *

  FROM TORDER

WHERE CUST_NO = '10001'

ORDER BY ORDER_DATE;

-- 인덱스 구성이 CUST_NO + ORDER_DATE인 경우 대체 가능