오라클에서 실행 계획을 보면 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최적화

오라클에서 SELECT COUNT(*)는 가장 단순한 집계 함수 중 하나입니다. 하지만 실무에서는 이 간단한 쿼리조차 예상보다 오래 걸리는 경우가 많습니다. 특히 대용량 테이블을 대상으로 할 때 COUNT가 심각하게 느려진다면, 단순히 "집계 함수니까 느리다"는 설명만으로는 부족합니다.

이번 글에서는 SELECT COUNT(*)가 느려지는 원인을 분석하고, 이를 개선할 수 있는 실질적인 전략들을 정리합니다.

1. COUNT(*)는 인덱스를 사용할까?

많은 개발자들이 SELECT COUNT(*)가 자동으로 인덱스를 사용할 것이라 생각하지만, 조건절이 없다면 테이블 전체를 스캔하게 됩니다. 오라클은 COUNT를 계산하기 위해 반드시 모든 행을 확인해야 하며, 조건이 없다면 풀 테이블 스캔(Full Table Scan)이 수행됩니다.

조건이 있는 경우, 해당 조건에 적절한 인덱스가 존재한다면 옵티마이저가 인덱스를 사용할 수도 있습니다.

예시:

SELECT COUNT(*) FROM EMP WHERE DEPTNO = 10;

위 쿼리는 DEPTNO 컬럼에 인덱스가 존재한다면 INDEX RANGE SCAN으로 수행될 수 있습니다.

2. COUNT가 느린 주요 원인

다음은 실무에서 자주 발생하는 COUNT 성능 저하 원인입니다:

• 조건이 없어 전체 테이블을 스캔하는 경우
• 대용량 테이블에 파티션이 없거나 적절하지 않은 경우
• 블록 내 저장된 행 수가 불균형하거나, CHAINED ROW가 많은 경우
• VIEW나 서브쿼리 내부의 조인 비용이 높을 경우
• DISTINCT, GROUP BY 등이 함께 쓰이는 경우

단순 COUNT일지라도 내부적으로 의외로 복잡한 연산 경로를 탈 수 있습니다. 실행 계획(EXPLAIN PLAN) 확인이 필요합니다.

3. 성능 개선 전략

✅ 조건을 최대한 활용하라

WHERE 절이 없는 COUNT는 테이블 전체를 스캔하기 때문에 매우 느립니다. 가능한 조건을 활용해 인덱스를 탈 수 있도록 쿼리를 구성하는 것이 좋습니다.

예시:

SELECT COUNT(*) FROM LOG_TABLE WHERE CREATE_DT >= SYSDATE - 1;

조건이 있다면 해당 컬럼에 인덱스를 추가하여 효율적인 COUNT가 가능합니다.

✅ 파티션 테이블 도입

대용량 로그성 테이블에서는 파티셔닝(partitioning)이 COUNT 성능에 큰 영향을 미칩니다. 예를 들어 일자별 RANGE 파티션을 구성하면, 최근 1일치 데이터에 대한 COUNT를 빠르게 조회할 수 있습니다.

✅ COUNT 대신 메타데이터 활용

전체 행 수만 필요하다면 DBA_TABLES, ALL_TABLES, USER_TABLES의 NUM_ROWS 컬럼을 사용할 수 있습니다. 단, 통계 정보가 최신이어야 정확합니다.

예시:

SELECT NUM_ROWS FROM USER_TABLES WHERE TABLE_NAME = 'EMP';

이 값은 DBMS_STATS.GATHER_TABLE_STATS 수행 시 갱신됩니다.

✅ COUNT 결과 캐싱 고려

변경이 자주 없는 테이블이나 집계용 로그 테이블의 경우, COUNT 결과를 별도 컬럼이나 캐시 테이블에 저장해두고 주기적으로 갱신하는 전략도 있습니다. 특히 대시보드 등에서 반복적으로 COUNT 쿼리가 실행된다면 효과적입니다.

마무리하며

SELECT COUNT(*)는 단순하지만, 그만큼 무심코 쓰기 쉬운 쿼리입니다. 특히 대용량 테이블에서는 성능 이슈로 이어지기 쉬우므로, COUNT 쿼리를 쓸 때도 항상 실행 계획을 점검하고 조건, 인덱스, 파티션 여부 등을 함께 고려해야 합니다.

이전에 작성한 INDEX가 안 잡힐 때 확인할 조건 글과 함께 참고하면, 쿼리의 성능 문제를 보다 체계적으로 점검할 수 있습니다.

#오라클 #SQL튜닝 #카운트성능 #대용량테이블 #파티셔닝 #DBMS_STATS #실무팁

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

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