[Oracle] 인덱스(Index), 왜 내 쿼리는 안 타는 걸까?

데이터베이스 성능 튜닝의 80%는 인덱스를 제대로 타느냐 마느냐에서 결정됩니다. 인덱스를 생성하고 통계 정보를 수집했음에도 불구하고, 옵티마이저가 인덱스를 버리고 Table Full Scan을 선택하는 이유를 완벽하게 정리해 드립니다.

---

1. 인덱스를 타는 경우 (Index Scan)

• 일치 검색 (=): 가장 이상적인 케이스입니다. 루트에서 리프까지 수직적 탐색 후 필요한 데이터만 쏙 뽑아옵니다.
  • WHERE ENAME = 'KING'
• 범위 검색 (Range Scan): 인덱스가 정렬되어 있으므로 특정 지점부터 옆으로(Horizontal) 읽어 나갑니다.
  • WHERE SAL BETWEEN 2000 AND 3000
  • WHERE ENAME LIKE 'S%' (전방 일치)
• 정렬 생략 (Order By Optimization): 인덱스는 이미 정렬되어 있으므로, ORDER BY 절에 인덱스 컬럼을 쓰면 오라클은 별도의 Sort 작업을 하지 않습니다.

---

2. 인덱스가 있는데도 안 타는 경우 (Full Scan?)

① 좌변 가공 (Column Transformation) 

인덱스 컬럼에 함수를 쓰거나 연산을 하면 인덱스 구조가 무너집니다.

• Bad: WHERE UPPER(ENAME) = 'KING'
• Bad: WHERE SAL * 12 > 50000
• Good: WHERE SAL > 50000 / 12 (연산은 우측 상수항에서 처리하세요!)

② 전치 불일치 (Leading Wildcard)

%가 앞에 붙으면 오라클은 인덱스의 어디서부터 시작해야 할지 모릅니다. 결국 처음부터 다 뒤져야 합니다.

• Bad: WHERE ENAME LIKE '%S'
• Good: WHERE ENAME LIKE 'S%'

③ 묵시적 형변환 (Implicit Type Conversion)

문자 컬럼에 숫자 조건을 주면 오라클이 내부적으로 함수를 씌워버립니다. (좌변 가공과 같은 효과)

• Bad: WHERE EMPNO = '1234' (EMPNO가 숫자형일 때)
• Good: WHERE EMPNO = 1234

④ 부정형 비교 (Negative Comparison)

"무엇이 아닌 것"을 찾는 것은 인덱스의 정렬 구조를 활용할 수 없습니다.

• Bad: WHERE DEPTNO != 10
• Bad: WHERE ENAME IS NOT NULL

---

3. 인덱스가 '범위'를 결정하는가?

전문가는 단순히 WHERE 절에 컬럼이 있는지만 보지 않습니다. "Index Range Scan"이 가능한지를 봅니다.

• Selectivity(선택도)의 함정: 인덱스가 있어도 조회하려는 데이터가 전체의 10~15%를 넘어가는 '흔한 값'이라면, 옵티마이저는 차라리 Full Table Scan이 빠르다고 판단합니다.
• Clustering Factor(군집도): 인덱스 순서와 실제 테이블 저장 순서가 얼마나 일치하느냐에 따라 인덱스 효율이 극명하게 갈립니다.

---

💡 요약: 인덱스 성공 법칙

1. 컬럼에 절대 손대지 마세요.
2. 데이터 타입 일치: 숫자는 숫자, 문자는 문자로 비교하세요.
3. 통계 정보 최신화: DBMS_STATS를 통해 오라클에게 최신 지도를 보여주세요.