[친절한 SQL 튜닝] 2. 인덱스 기본 [3]
2025. 1. 15. 23:11

 
 

2.3.1 Index Range Scan

  • Index Range Scan은 BTree 인덱스의 가장 일반적이고 정상적인 형태의 액세스 방식이다.
  • 아래 그림처럼 인덱스 루트에서 리프 블록까지 수직적으로 탐색한 후에 '필요한 범위(Range)만' 스캔한다.
  • 선두 컬럼을 가공하지 않은 상태로 조건절에 사용

성능은 인덱스 스캔 범위, 테이블 액세스 횟수를 얼마나 줄일 수 있느냐로 결정된다.(인덱스 잘 탄다고 되는 게 아님!)
 

Index Range Scan

 
 

2.3.2 Index Full Scan

  • Index Full Scan은 아래 그림처럼 수직적 탐색없이 인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색하는 방식이다.
  • 대개 데이터 검색을 위한 최적의 인덱스가 없을 때 차선으로 선택된다.
  • 인덱스 선두 컬럼이 조건절에 없으면 옵티마이저는 먼저 Table Full Scan을 고려한다. 하지만 대용량 테이블이면 인덱스 활용을 고려하지 않을 수 있다.
  • 조건절의 데이터량이 소량이라면 Index Full Scan을 통한 필터링이 효과적이다.
Index Full Scan

 
 
 

2.3.3 Index Unique Scan

  • 수직적 탐색만으로 데이터를 찾는 스캔 방식으로서, Unique 인덱스를 "=" 조건으로 탐색하는 경우에 작동한다.
  • Unique Index가 존재하는 컬럼은 중복 값이 입력되지 않게 DBMS가 데이터 정합성을 관리해 준다.
  • Unique Index라고 해도 범위검색 조건(BETWEEN, 부등호, LIKE)으로 검색할 때는 Index Range Scan으로 처리된다.
  • 또한 Unique 결합 인덱스에 대해 일부 컬럼만으로 검색할 때도 Index Range Scan으로 처리된다.
Index Unique Scan

 
 

2.3.4 Index Skip Scan

  • 인덱스 선두 컬럼을 조건절에 사용하지 않으면 옵티마이저는 기본적으로 Table Full Scan을 선택한다.
  • Table Full Scan보다 I/O를 줄일 수 있거나 정렬된 결과를 쉽게 얻을 수 있다면, Index Full Scan을 사용하기도 한다.
  • 오라클 9i 버전부터 인덱스 선두 컬럼이 조건절에 없어도 인덱스를 활용하는 새로운 스캔 방식을 선보였는데, 이것이 Index Skip Scan이다.
  • 조건절에 빠진 인덱스 선두 컬럼의 Distinct Value 개수가 적고 후행 컬럼의 Distinct Value 개수가 많을 때 유용하다.
  • 힌트 : index_ss, no_index_ss
  • 루트 또는 브랜치 블록에서 읽은 컬럼 값 정보를 이용해 조건절에 부합하는 레코드를 포함할 "가능성이 있는" 리프 블록만 골라서 액세스 하는 스캔 방식이다.

 


      
SELECT /*+ INDEX_SS(사원 사원_IDX) */
       *
  FROM 사원
WHERE 연봉 BETWEEN 2000 AND 4000
     ;

 
위 그림 인덱스 루트 블록에서 첫 번째 레코드가 가리키는 리프 블록은 [남&800] 이하인 레코드를 담고 있다. 이 블록은 스킵하고 두 번째 블록도 스킵한다. 세 번째 레코드가 가리키는 리프 블록은 [남&1500] 이상이면서 [남&5000] 이하인 레코드를 담고 있으므로 액세스한다. 네 번째 블록과 다섯 번째 블록도 조건절에 부합하지 못하므로 스킵한다.
 
여섯 번째 리프 블록의 액세스 여부를 이해하는 것이 중요하다. 여섯 번째 리프 블록은 [남&10000] 이므로 [2000<= 연봉 <= 4000] 구간을 초과한다. 따라서 스킵해도 될 것 같지만 액세스를 해야 한다. 여자 중에서 [연봉 <3000] 이거나 "남"과 "여" 사이에 다른 성별이 혹시 존재한다면 이 리프 블록은 저장되고, 연봉 = 3000인 여자 직원도 뒤쪽에 일부 저장돼 있을 수 있기 때문이다. 일곱 번째부터 아홉 번째 레코드가 가리키는 리프 블록은 스킵해도 된다.
 
마지막으로 열 번째 리프 블록은 조건절에 부합하지 못하므로 스킵해도 될 것 같지만 "여"보다 큰 성별의 값이 존재한다면 여기에 모두 저장될 것이므로 액세스해야 한다. (성별이 남,여만 있다는 것을 컴퓨터는 모름) 
 

Index Skip Scan

 
 

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

인덱스 구성이 다음과 같다고 하자.
 
일별업종별거래_PK : 업종유형코드+업종코드+기준일자
 
아래 SQL처럼 선두 컬럼(=업종유형코드)에 대한 조건절은 있고, 중간 컬럼(=업종코드)에 대한 조건절이 없는 경우에도 Skip Scan을 사용할 수 있다.
 


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

 
만약 위 SQL에서 Index Range Scan을 사용한다면, 업종유형코드 = '01'인 인덱스 구간을 모두 스캔해야 한다.
Index Skip Scan을 사용한다면, 업종유형코드 = '01'인 구간에서 기준일자가 '20080501'보다 크거나 같고 '20080531'보다 작거나 같은 레코드를 '포함할 가능성이 있는 리프 블록만' 골라서 액세스할 수 있다.
 
Distinct Value가 적은 두 개의 선두컬럼이 모두 조건절에 없는 경우에도 유용하게 사용할 수 있다.


      
SELECT /*+ INDEX_SS(A 일별업종별거래_PK) */  --- Index Skip Scan 사용 가능
   기준일자, 업종코드, 체결건수, 체결수량, 거래대금
  FROM 일별업종별거래 A
WHERE 기준일자 BETWEEN '20080501' AND '20080531'

 
또한 선두 컬럼이 부등호, BETWEEN, LIKE 같은 범위검색 조건일 때도 사용 가능하다.


      
SELECT /*+ INDEX_SS(A 일별업종별거래_PK) */  -- 선두컬럼이 범위검색일 경우도 사용 가능
   기준일자, 업종코드, 체결건수, 체결수량, 거래대금
  FROM 일별업종별거래 A
WHERE 기준일자 BETWEEN '20080501' AND '20080531'

 
만약 위 SQL에서 Index Range Scan을 사용한다면, 기준일자 BETWEEN 조건을 만족하는 인덱스 구간을 모두 스캔해야 한다. Index Skip Scan을 사용한다면, 기준일자 BETWEEN 조건을 만족하는 인덱스 구간에서 업종유형코드 = '01'인 레코드를 '포함할 가능성이 있는 리프 블록만' 골라서 액세스할 수 있다.
 
이처럼 Index Range Scan이 불가능하거나 효율적이지 못한 상황에서 Index Skip Scan이 종종 빛을 발한다. 부분범위 처리가 가능하다면 Index Full Scan이 도움이 되기도 한다. 
 
 

2.3.5 Index Fast Full Scan

Index Fast Full Scan은 논리적인 인덱스 트리 구조를 무시하고 인덱스 세그먼트 전체를 Multiblock I/O 방식으로 스캔하기 때문에 Index Full Scan보다 빠르다.
힌트는 index_ffs, no_index_ffs로 사용하면 된다.
 
Index Fast Full Scan은 Multiblock I/O 방식을 사용하므로 디스크로부터 대량의 인덱스 블록을 읽어야 할 때 큰 효과를 발휘한다. 속도는 빠르지만, 인덱스 리프 노드가 갖는 연결 리스트 구조를 무시한 채 데이터를 읽기 때문에 결과집합이 인덱스 키 순서대로 정렬되지 않는다. 
 
 Index Range Scan 또는 Index Full Scan과 달리, 인덱스가 파티션 돼 있지 않더라도 병렬 쿼리가 가능한 것도 중요한 특징이다. 병렬 쿼리 시에는 Direct Path I/O 방식을 사용하기 때문에 I/O 속도가 더 빨리진다.
 

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

 
 
 

2.3.6 Index Range Scan Descending

Index Range Scan과 기본적으로 동일한 스캔 방식이다. 아래 그림처럼 인덱스를 뒤에서부터 앞쪽으로 스캔하기 때문에 내림차순으로 정렬된 결과집합을 얻는다는 점만 다르다.

Index Range Scan Descending

 
 


      
SELECT *
  FROM EMP
WHERE EMPNO > 0
ORDER BY EMPNO DESC

 
위 쿼리처럼 EMPNO 기준으로 내림차순 정렬하고자 할 때 EMPNO 컬럼에 인덱스가 있으면 옵티마이저가 알아서 인덱스를 거꾸로 읽는 실행계획을 수립한다. 만약 옵티마이저가 인덱스를 거꾸로 읽지 않는다면, index_desc 힌트를 이용해 유도할 수 있다.
 
 


      
CREATE INDEX EMP_X02 ON EMP(DEPTNO, SAL);
SELECT DEPTNO, DNAME, LOC
     , (SELECT MAX(SAL)
          FROM EMP
         WHERE DEPTNO = D.DEPTNO
       )
  FROM DEPT D

 
위 쿼리처럼 MAX 값을 구하고자 할 때도 해당 컬럼에 인덱스가 있으면 인덱스를 뒤에서부터 한 건만 읽고 멈추는 실행계획이 자동으로 수립된다.