오라클에서 실행 계획을 보면 INDEX RANGE SCAN이 잡히는 경우가 있습니다. 일반적으로 이는 인덱스를 잘 활용하고 있다는 의미로 받아들여지지만, 실제로는 쿼리 성능이 기대만큼 나오지 않거나 오히려 느려지는 경우도 존재합니다.

이번 글에서는 인덱스를 타고 있음에도 성능이 저하되는 이유를 다양한 관점에서 분석하고, 이를 해결하기 위한 실무적인 팁을 함께 소개합니다.

1. INDEX RANGE SCAN이란?

INDEX RANGE SCAN은 인덱스를 통해 일부 범위만 탐색하는 방식입니다. 예를 들어 조건절이 WHERE AGE BETWEEN 20 AND 30처럼 범위 조건일 때 사용됩니다.

실행 계획에서 다음처럼 보입니다:

INDEX RANGE SCAN on IDX_EMP_AGE

인덱스가 사용되므로 성능이 좋을 것으로 기대되지만, 아래와 같은 이유로 성능이 떨어질 수 있습니다.

2. 인덱스를 타는데 느린 이유

① TABLE ACCESS BY ROWID가 병목

인덱스를 타더라도 최종 데이터를 읽기 위해 테이블 본문을 조회해야 하는 경우가 많습니다. 이는 TABLE ACCESS BY ROWID 단계에서 발생하며, 인덱스로 찾은 ROWID를 따라가 테이블을 반복해서 읽는 구조입니다.

행 수가 많다면 디스크 I/O가 상당히 증가합니다.

② 인덱스 조건은 탔지만 FILTER 조건에서 제외됨

예를 들어 다음 쿼리에서:

SELECT * FROM EMP
WHERE AGE BETWEEN 30 AND 40 AND GENDER = 'M';

AGE 컬럼에만 인덱스가 있을 경우, GENDER = 'M' 조건은 인덱스에서 필터링되지 않고, 테이블 조회 후 FILTER 단계에서 제거됩니다. 불필요한 행을 먼저 읽는 구조가 되므로 비효율적입니다.

③ 인덱스 컬럼이 NULLABLE이거나 정렬도가 낮음

조건 컬럼이 NULL 값을 많이 포함하거나, 정렬도가 낮은 컬럼일 경우 오라클 옵티마이저는 예측보다 많은 범위를 탐색하게 됩니다. 결국 인덱스를 사용하고도 실제 I/O 비용은 높은 상황이 됩니다.

④ 조건 범위가 지나치게 넓음

INDEX RANGE SCAN이라도 WHERE AGE > 0 같은 조건이라면 거의 전체 테이블을 읽는 것과 다름없는 범위를 탐색하게 됩니다. 이럴 경우에는 오히려 FTS(FULL TABLE SCAN)가 더 빠를 수 있습니다.

3. 개선 방법

✅ 필요한 컬럼만 SELECT

필요한 컬럼만 조회하면 INDEX ONLY SCAN으로 전환할 수 있습니다. 이 경우 테이블을 조회하지 않고 인덱스만으로 결과를 반환하므로 매우 빠릅니다.

예시:

SELECT AGE FROM EMP WHERE AGE BETWEEN 20 AND 30;

AGE만 필요하면 INDEX ONLY SCAN이 가능해집니다.

✅ 복합 인덱스 설계

위 예시처럼 AGE와 GENDER를 모두 조건으로 사용하는 경우, 두 컬럼을 포함하는 복합 인덱스를 설계하면 FILTER 단계를 제거할 수 있습니다.

CREATE INDEX IDX_EMP_AGE_GENDER ON EMP(AGE, GENDER);

✅ 인덱스 힌트와 실행 계획 확인

실행 계획을 항상 확인하여 실제 인덱스가 어떤 방식으로 사용되는지 점검해야 합니다. 때로는 옵티마이저가 인덱스를 사용하더라도 성능상 불리할 수 있으며, 이럴 경우 힌트를 통해 명시적으로 FTS로 유도하는 것도 방법입니다.

마무리하며

실행 계획에 INDEX RANGE SCAN이 떴다고 해서 반드시 빠른 쿼리라고 단정지을 수는 없습니다. 실제 쿼리 성능은 테이블 접근 방식, 조건의 범위, 필터 조건 처리 방식 등 다양한 요소가 복합적으로 작용합니다.

이전에 작성한 INDEX가 안 잡힐 때 확인할 조건 및 SELECT COUNT(*)가 느릴 때 – 성능 개선 전략과 대안 글과 함께 참고하면, 인덱스 관련 성능 이슈를 보다 정확하게 진단하고 해결할 수 있습니다.

#오라클 #SQL튜닝 #인덱스성능 #INDEXRANGESCAN #실행계획 #실무팁 #SQL최적화

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

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튜닝 #인덱스미사용 #오라클쿼리 #형변환 #통계정보 #함수사용주의 #실무팁