[SQL 튜닝] 인덱스 기본 사용법

Index Range Scan VS Index Full Scan

- Leaf블록 스캔 시작점을 알 수 있냐 없냐

 

1. Index Range Scan

- Leaf 블록의 일부만 스캔

- 인덱스 컬럼 (선두 컬럼)을 가공하지 않아야 인덱스를 정상적으로 사용할 수 있음

- 정상적으로 사용 = 수직적 탐색을 통해 Leaf블록에서 스캔 시작점을 찾고 거기서부터 수평적 탐색을 하다가 멈추는 것

 

2. Index Full Scan

- 인덱스 컬럼을 가공해도 인덱스를 사용할 수는 있지만, 스캔 시작점을 찾을 수 없고, 모든 Leaf 블록을 전부 스캔해야 함

 

=> 인덱스 컬럼을 가공하면  Index Range Scan을 사용할 수 없음

- 인덱스 컬럼을 가공했을 때 인덱스를 정상적으로 사용할 수 없는 이유는 인덱스 스캔 시작점을 찾을 수 없기 때문임

- Index Range Scan을 특정 Range만 스캔한다는 것으로 Range에는 시작점과 끝점이 있음

- 인덱스는 가공되지 않은 값이 저장되어 있는데, 가공된 값을 가지고 검색을 하려면 시작점을 특정 지을 수 없음

- OR와 IN은 옵티마이저의 쿼리 변환 기능을 통해 Range Scan이 가능할 수도 있음

where substr(생년월일, 5, 2) = '05' 
-- 5월에 태어난 사람, substr로 가공함

where nvl(주문수량, 0) < 100 
-- nvl로 가공함

where like '%대한%' 
-- '대한'으로 시작하면 Range Scan이 가능하지만 '대한'을 포함하는 값은 흩어져 있음

where (전화번호 = tel_no OR 고객명 = cust_nm)
-- 수직적 탐색을 통해 전화번호가 '01012345678' 이거나 이름이 '홍길동'인 어느 한 지점을 특정할 수 없음

where 전화번호 in (tel_no1, tel_no2)
-- in은 or와 같은 것임

 

인덱스 사용 조건

- Index Range Scan을 하기 위한 가장 첫 번째 조건은 인덱스 선두 컬럼이 가공되지 않은 상태로 조건절에 있어야 함

 

Ex:

인덱스 : [소속팀 + 사원명 + 연령] 

= 데이터를 소속팀 순으로 정렬하고, 소속팀이 같으면 사원명으로 정렬하고, 사원명도 같으면 연령으로 정렬함

select 사원번호, 소속팀, 연령, 입사일, 전화번호
from 사원
where 사원명 = '홍길동'

-- 인덱스 선두 컬럼인 소속팀이 조건절 첫 줄에 없으므로 Index Range Scan은 불가능
-- 이름이 같은 사원이더라도 소속팀이 같으면 멀리 떨어져 있으므로 Index Full Scan해야함 (Leaf 블록 전 구간에 흩어짐)

 

Ex:

인덱스 : [기준연도 + 과세구분코드 + 보고회차 + 실명확인번호]

select * from TXA1234
where 기준연도 = stdr_year
and substr(과세구분코드, 1, 4) = txtn_dcd
and 보고회차 = rpt_tmrd
and 실명확인번호 = rnm_cnfm_no

-- 인덱스 컬럼 중하나인 과세구분코드가 substr로 가공되었지만,
-- 인덱스 선두 컬럼인 기준연도는 가공되지 않은 상태로 조건절에 있어서 Index Range Scan이 가능

 

- 인덱스를 탄다라는 말은 Index Range Scan을 한다는 뜻인데, Index Range Scan을 한다고 무조건 성능이 좋은 것이라고 할 수 없음

- Index Range Scan을 하며 인덱스를 잘 타는 것 같아도, 실제로 인덱스를 정말 잘 활용하는지는 인덱스 리프 블록에서 스캔하는 양을 따져봐야 함