Oracle - 인덱스를 사용하지 않는 쿼리

배경

오라클에서 인덱스를 사용하더라도 인덱스를 사용하지 않는 쿼리가 있다. 현재 프로젝트의 레거시 코드도 확인해보면, 쿼리가 비효율적으로 작성되어 성능 문제가 발생하는 경우가 많았다. 오라클 쿼리를 작성할 때나 쿼리 튜닝할 때 알아두면 좋을 것 같아 정리해본다.

 

개발 환경

• Oracle 11g
• DBeaver

 

복합 인덱스

복합 인덱스의 선행 컬럼을 WHERE 절에서 사용하지 않으면 인덱스가 무시된다.

WHERE 절에 사용된 컬럼이 복합 인덱스의 선행 컬럼이 아니라면 INDEX SKIP SCAN과 같은 특수한 상황을 제외하고는 FULL SCAN을 사용한다. 그렇기 때문에 복합 인덱스를 생성할 때 WHERE 절에 자주 포함되는 컬럼을 선행 컬럼으로 두는 것이 좋다.

INDEX SKIP SCAN은 복합 인덱스의 선행 컬럼이 WHERE 절에 없더라도 두 번째 인덱스 컬럼으로 액세스하는 방식이다.

-- Index Columns (LAST_NAME, FIRST_NAME)
SELECT
    USER_ID, FIRST_NAME, LAST_NAME
FROM USERS
WHERE FIRST_NAME = 'Kim'

-- Index Columns (LAST_NAME, FIRST_NAME)
SELECT
    USER_ID, FIRST_NAME, LAST_NAME
FROM USERS
WHERE FIRST_NAME = 'Kim' AND LAST_NAME = 'Chan-Bin

재밌는건 앞 부분의 컬럼만 WHERE 절에 사용하도 인덱스가 적용된다.

-- Index Columns (LAST_NAME, FIRST_NAME)
SELECT
    USER_ID, FIRST_NAME, LAST_NAME
FROM USERS
WHERE LAST_NAME = 'Chan-Bin'

 

함수, 연산자사용

WHERE 절에서 컬럼에 함수를 적용하면 인덱스를 사용할 수 없다. 컬럼에 함수를 포함시키면 Oracle은 테이블에서 각 행을 읽고 함수를 사용해 값을 계산한 후에 제공된 값과 비교를 수행해야 한다. 인덱스 값이 아닌 함수로 계산된 인덱스 값과 비교해야 하기 때문이다.

-- Index Columns (CREATED_AT)
SELECT
    USER_ID, FIRST_NAME, LAST_NAME
FROM USERS
WHERE TO_CHAR(CREATED_AT, 'YYYY-MM-DD') = '2025-02-11'

-- Index Columns (CREATED_AT)
SELECT
    USER_ID, FIRST_NAME, LAST_NAME
FROM USERS
WHERE CREATED_AT LIKE '20250211%'

 

만약 함수나 연산자를 반드시 사용해야 한다면 함수나 연산자를 적용한 값을 인덱스로 설정할 수 있다.

-- Index Columns (TO_CHAR(CREATED_AT, 'YYYY-MM-DD'))
SELECT
    USER_ID, FIRST_NAME, LAST_NAME
FROM USERS
WHERE TO_CHAR(CREATED_AT, 'YYYY-MM-DD') = '2025-02-11'

 

LIKE 문의 선행 와일드 카드

LIKE 연산자와 함께 선행 와일드카드를 사용하면 인덱스를 사용하지 않는다. 인덱스는 B-Tree 구조를 사용하여 키 값을 기준으로 정렬되어 있는데, 앞에 와일드카드가 붙으면 어떤 문자로 시작하는지 알 수 없어 인덱스의 정렬 순서를 사용할 수 없다.

-- Index Columns (LAST_NAME)
SELECT
    USER_ID, FIRST_NAME, LAST_NAME
FROM USERS
WHERE LAST_NAME LIKE '%BIN%'

-- Index Columns (LAST_NAME)
SELECT
    USER_ID, FIRST_NAME, LAST_NAME
FROM USERS
WHERE LAST_NAME LIKE 'BIN%'

 

부정 조건

NOT IN, NOT EXISTS, != 등의 부정 조건은 인덱스 사용을 방해한다. IN 절은 인덱스 중 전달받은 값과 일치하는지 인덱스 값만 찾으면 되지만 NOT IN절은 모든 테이블을 확인하며 전달받은 값과 일치하지 않는지 확인해야 한다.

-- Index Columns (LAST_NAME)
SELECT
    USER_ID, FIRST_NAME, LAST_NAME
FROM USERS
WHERE LAST_NAME NOT IN ('Chan-Bin')

-- Index Columns (LAST_NAME)
SELECT
    USER_ID, FIRST_NAME, LAST_NAME
FROM USERS
WHERE LAST_NAME IN ('Chan-Bin')

 

낮은 선택도

쿼리가 조회해 오는 데이터가 테이블의 10-20% 이상을 반환하는 경우, Optimizer는 Full Scan 방식을 선택할 수 있다.

-- Index Columns (SEX)
SELECT
    USER_ID, FIRST_NAME, LAST_NAME
FROM USERS
WHERE SEX = 'M'

 

OR 사용

WHERE에서 각 OR절마다 인덱스를 사용하게 되면 중복된 행을 처리해야 하는 비효율적인 문제가 발생한다. AND였다면 첫 번째 조건문이 만족되지 않으면 바로 바로 다음 로우를 체크하면되지만 OR 절을 사용하면 하나의 조건이라도 만족하는지 모두 확인해야 한다.

Optimizer는 이러한 경우 각 인덱스를 사용하는 대신 풀 스캔 방식을 사용할 수 있다. OR을 사용하기보다 UNION을 사용하면 각 쿼리에 대한 인덱스를 효율적으로 사용할 수 있다.

-- Index Columns (LAST_NAME)
SELECT
    USER_ID, FIRST_NAME, LAST_NAME
FROM USERS
WHERE LAST_NAME = 'Chan-Bin' OR FIRST_NAME = 'Park'

-- Index Columns (LAST_NAME)
SELECT
    USER_ID, FIRST_NAME, LAST_NAME
FROM USERS
WHERE LAST_NAME = 'Chan-Bin'
UNION
SELECT
    USER_ID, FIRST_NAME, LAST_NAME
FROM USERS
WHERE FIRST_NAME = 'Park'

 

마무리

인덱스 사용 여부는 옵티마이저가 데이터 분포, 테이블 크기 등 다양한 요소를 고려하여 가장 효율적인 실행 계획을 선택한다. 하지만 이러한 특성을 이해하고 쿼리를 작성하거나 튜닝할 때 더 효율적인 쿼리를 설계할 수 있다.

성능 문제 발생 시 원인을 파악하고 해결하는 데 큰 도움이 될 거라고 생각한다. 다음에는 테이블 스캔 종류에 대해 공부해봐야겠다.

참조

https://serhatcelik.wordpress.com/2022/05/29/why-oracle-not-using-index/

Why Oracle Not Using Index?