[친절한 SQL 튜닝] 2.2 인덱스를 Range Scan 할 수 없는 이유

인덱스를 정상적을 사용한다는 의미?

• 인덱스를 정상적으로 사용한다는 의미는
  인덱스 리프 블록의 일부만 스캔한다는 의미(Index Range Scan)이고,
  이를 위해서는 리프 블록에서 스캔 시작점을 찾아 스캔하다가 중간에 멈추는 것이다.
  (즉, 스캔 시작점과 끝 지점이 존재한다.)
• Index Range Scan을 위해서는 인덱스 컬럼을 가공해서는 안된다.
• ⚠️ 인덱스 컬럼을 가공할 시 리프 블록 전체를 스캔하는 Index Full Scan이 발생한다.

인덱스를 Range Scan할 수 없는 이유

• 인덱스 컬럼을 가공했을 때 인덱스를 정상적으로 사용할 수 없는 이유는
  인덱스 스캔 시작점을 찾을 수 없기 때문이다.
• Index Range Scan은 인덱스에서 일정 범위를 스캔한다는 뜻이고,
  일정 범위를 스캔하려면 시작 지점과 끝지점이 있어야 한다.

인덱스를 Range Scan할 수 없는 7가지 경우

1. SUBSTR() 함수 사용

WHERE SUBSTR(생년월일, 5, 2) = '05'

• 인덱스는 원본 값에 대해서만 저장
• 컬럼에 변형 함수를 적용하면, 원본 값과 달라지기 때문에
  인덱스를 이용한 Range Scan을 할 수 없고, Index Full Scan 또는 Table Full Scan을 수행

2. NVL() 함수 사용

WHERE NVL(주문수량, 0) < 100

• NVL() 함수로 원본 값이 변형되어,
  인덱스 원본 값과 직접 비교가 불가능(Range Scan 불가능)
• Oracle은 NVL() 함수 사용시 Table Full Scan 발생
• 👀 예외적으로 커버링 인덱스인 경우 Index Full Scan 가능성

3. LIKE %키워드%

WHERE 업체명 LIKE '%대한%'

• %가 앞에 있으면 문자열 시작 지점이 불확실하므로
  인덱스를 통한 순차적 접근(Range Scan) 불가능
• 대한%처럼 끝이 확실한 경우 Range Scan이 가능
• %로 시작하면 Table Full Scan이 수행됨

4. OR 조건 사용

WHERE 전화번호 = :tel_no OR 고객명 = :cust_nm

• OR조건은 각각의 조건을 따로 평가해 인덱스 효율성을 저하
• 전화번호가 '01012345678' 이거나 고객명이 '홍길동'인
  어느 한 시작지점을 바로 찾을 수 없음

✨ OR Expansion

• 여러 조건이 OR 연산자로 결합된 경우,
  오라클이 각각의 조건을 별도로 수행한 뒤 그 결과를 합치는 방식
• 옵티마이저가 내부적으로 쿼리를 나누어 수행
  • OR로 연결된 각 조건이 각각 별도의 인덱스 접근 가능성을 가질 때
  • OR로 연결된 조건이 복잡하지 않고 단순하게 독립적 형태일 때

SELECT * 
FROM 고객
WEHRE 고객명 = :cust_nm		-- 고객명이 선두 컬럼인 인덱스 Range Scan 
UNION ALL
SELECT * 
FROM 고객
WHERE 전화번호 = :tel_no 		-- 전화번호가 선두 컬럼인 인덱스 Range Scan
AND (고객명 <> :cust_nm OR 고객명 IS NULL)

• 즉, 각각의 조건을 별도로 인덱스를 활용해 빠르게 접근한 후 결과를 합쳐서 리턴

5.IN 절 사용

WHERE 전화번호 IN ( :tel_no1, :tel_no2)

• IN은 OR을 표현하는 다른 방식일 뿐이므로 Range Scan이 불가능하다.

SELECT * 
FROM 고객
WHERE 전화번호 = :tel_no1
UNION ALL
SELECT *
FROM 고객
WHERE 전화번호 = :tel_no2

• IN 조건도 위와 같이 UNION ALL 방식으로 작성하면,
  각 브랜치 별로 인덱스 스캔 시작점을 찾을 수 있어 Range Scan이 가능하다.
• 따라서 옵티마이저는 IN 조건절에 대해 IN-List Iterator 방식을 사용한다.

✨ IN-List Iterator

• IN 절에 여러 개의 값을 나열했을 때,
  오라클이 각 값마다 독립적으로 인덱스를 통해 접근하는 메커니즘
• 옵티마이저가 내부적으로 각각 독립적 접근을 수행
  • IN절의 값이 상대적으로 적을 때
  • 각 값마다 인덱스를 통한 접근이 효율적일 때

6. NULL 비교

SELECT * FROM 테이블 WHERE col IS NULL;

• 인덱스에 NULL 값은 저장되지 않거나(Oracle) 저장되더라도 일반적인 값과 구분됨
• NULL은 특정 값으로 비교가 어려워(정렬 기준이 모호) 효율적인 Range Scan이 불가능
• 별도의 NULL을 포함하는 인덱스 또는 Table Full Scan으로 처리됨

7. NOT 조건

SELECT * FROM 테이블 WHERE col != 'A';

• 인덱스는 기본적으로 "포함" 조건으로 데이터를 탐색
• 반면에 "제외" 조건인 NOT은 전체를 확인해야 하므로
  인덱스를 활용한 Range Scan이 어려움
• 일반적으로 Table Full Scan 발생

ref. https://product.kyobobook.co.kr/detail/S000001975837

포스팅하다가 궁금해서 찾아본 내용의 포스팅

LIKE %KEYWORD% 연산과 인덱스

커버링 인덱스란?

NULL과 인덱스(NVL()함수 사용과 NULL 비교

INDEX FULL SCAN과 TABLE FULL SCAN