INDEX가 안 잡힐 때 확인할 조건 – 오라클 인덱스 미사용 원인 정리

오라클에서 인덱스를 생성했음에도 불구하고 실제 실행 계획에서 인덱스가 사용되지 않는 경우는 실무에서 자주 발생하는 문제입니다. 이 글에서는 인덱스가 정상적으로 사용되지 않을 때 확인해야 할 주요 원인들을 정리하고, 각각의 사례에 대한 설명과 개선 방법을 함께 다룹니다.

1. WHERE 절의 컬럼에 함수 사용

인덱스가 설정된 컬럼에 함수가 사용되면 옵티마이저는 해당 인덱스를 사용할 수 없습니다.

예시:

SELECT * FROM EMP WHERE TO_CHAR(HIREDATE, 'YYYY') = '2020';

위 쿼리는 HIREDATE 컬럼에 함수가 적용되어 있어 인덱스를 타지 않습니다. 가능한 방법은 함수를 제거하거나, 함수 기반 인덱스(function-based index) 를 따로 생성하는 것입니다.

2. 형 변환 발생

숫자 타입 컬럼에 문자열을 비교하거나, 반대로 문자열 컬럼에 숫자를 비교하는 경우 암묵적 형 변환이 일어나 인덱스가 무시될 수 있습니다.

예시:

SELECT * FROM EMP WHERE EMPNO = '7369';  -- EMPNO가 NUMBER형일 경우

이 경우 '7369'가 암묵적으로 TO_NUMBER 처리되며 인덱스가 무시됩니다. 조건절의 리터럴 타입을 컬럼 타입과 맞추는 것이 중요합니다.

3. NOT 조건, 부정 연산자

!=, NOT IN, NOT EXISTS 등 부정 조건이 들어가는 경우 인덱스 사용률이 현저히 낮아집니다. 특히 범위 탐색이 불가능한 조건들은 옵티마이저가 풀 스캔을 선택하는 경향이 있습니다.

가능한 경우 NOT IN 대신 OUTER JOIN + IS NULL 방식 등으로 구조를 바꾸는 것이 도움이 됩니다.

4. 컬럼에 NULL 조건

WHERE COLUMN = NULL 또는 IS NOT NULL 조건도 인덱스 사용을 방해합니다. 오라클 인덱스는 NULL 값을 기본적으로 저장하지 않기 때문에, IS NULL 조건도 인덱스를 타지 않을 가능성이 높습니다.

NULL이 포함되는 조건을 자주 조회한다면 비트맵 인덱스를 고려할 수 있습니다.

5. OR 조건

WHERE A = 'X' OR B = 'Y' 처럼 OR 조건이 있는 경우 옵티마이저는 전체 조건에 대해 풀스캔을 선택할 수 있습니다. 이 경우는 쿼리를 UNION ALL로 분리하는 방식으로 인덱스 사용을 유도할 수 있습니다.

예시:

SELECT * FROM EMP WHERE A = 'X'
UNION ALL
SELECT * FROM EMP WHERE B = 'Y';

6. 통계 정보 미갱신

DBMS_STATS를 통해 테이블 및 인덱스의 통계 정보가 최신 상태로 유지되지 않으면 옵티마이저가 잘못된 판단을 할 수 있습니다.

정기적으로 통계를 갱신하는 스케줄을 운영하는 것이 중요합니다.

7. 인덱스 컬럼 순서 불일치

다중 컬럼 인덱스의 경우, WHERE 절 조건이 인덱스의 선두 컬럼부터 일치해야만 해당 인덱스가 유효하게 작동합니다.

예시: 인덱스 (COL1, COL2)일 때

SELECT * FROM TAB WHERE COL2 = 'Y'; -- 인덱스 사용 불가

COL1 조건이 빠지면 옵티마이저는 인덱스를 사용할 수 없습니다. 컬럼 순서를 기준으로 조건절을 구성해야 합니다.

---

위에서 다룬 원인들은 대부분 실무에서 흔하게 접할 수 있는 사례입니다. 오라클에서 인덱스가 사용되지 않을 경우, 실행 계획만 보는 것에 그치지 않고 이러한 조건들을 하나씩 점검해보는 습관이 필요합니다. 특히 인덱스를 기준으로 튜닝이 필요한 경우, 조건절을 어떻게 작성하느냐가 성능에 직접적인 영향을 주는 핵심 요소입니다.

이전에 작성한 HAVING 절과 WHERE 절의 차이 글처럼, 조건을 어떻게 거느냐는 쿼리 성능에 큰 차이를 만들 수 있습니다. 인덱스 미사용 문제가 발생했다면 위 내용을 기준으로 체크리스트처럼 점검해보는 것을 추천합니다.

#오라클 #SQL튜닝 #인덱스미사용 #오라클쿼리 #형변환 #통계정보 #함수사용주의 #실무팁