인덱스 스캔 효율화와 설계

테이블 액세스 최소화가 "테이블에서 버려지는 건수"를 줄이는 일이라면, 인덱스 스캔 효율화는 "인덱스에서 읽고 버리는 건수"를 줄이는 일이다. 둘 다 결합 인덱스의 컬럼 구성과 순서에 크게 좌우된다.

인덱스 선행 컬럼과 스캔 범위

결합 인덱스는 선두 컬럼부터 차례로 정렬된다. 선두 컬럼이 같을 때만 두 번째 컬럼으로 정렬되고, 두 번째까지 같을 때만 세 번째 컬럼으로 정렬된다.

CREATE INDEX 주문_X1 ON 주문 (주문일자, 고객번호, 상품코드);

이 구조 때문에 다음 원칙이 성립한다.

인덱스 액세스 조건과 필터 조건

실행계획의 Predicate Information에서 조건이 어떻게 쓰였는지 확인할 수 있다.

-- 인덱스: 주문_X1 (주문일자, 고객번호, 상품코드)
SELECT * FROM 주문
WHERE  주문일자 BETWEEN '20240101' AND '20240131'
AND    고객번호 = 'C01'
AND    주문금액 >= 10000;

| Id | Operation                           | Name   |
|* 1 |  TABLE ACCESS BY INDEX ROWID BATCHED| 주문    |
|* 2 |   INDEX RANGE SCAN                  | 주문_X1 |
Predicate Information
   1 - filter("주문금액">=10000)                          ← 테이블 필터
   2 - access("주문일자">='20240101' AND "고객번호"='C01'
              AND "주문일자"<='20240131')
       filter("고객번호"='C01')                           ← 인덱스 필터

시험 포인트: 인덱스 스캔 효율은 인덱스 액세스 조건이 결정하고, 테이블 랜덤 액세스 횟수는 인덱스 액세스 + 인덱스 필터 조건이 결정한다. 테이블 필터 조건은 최종 결과 건수에만 영향을 준다.

비교 연산자와 컬럼 순서에 따른 효율

같은 조건이라도 인덱스 컬럼 순서에 따라 스캔량이 크게 달라진다.

WHERE 고객번호 = 'C01'
AND   주문일자 BETWEEN '20240101' AND '20240131'

시험 포인트: = 조건 컬럼을 앞에, 범위 조건 컬럼을 뒤에 두면 스캔 범위가 최소화된다. 결합 인덱스 컬럼 순서 문제에서 가장 많이 나오는 원리다.

BETWEEN을 IN-List로 바꾸기

범위 조건이 선행 컬럼에 있어 후행 컬럼 조건이 필터 역할밖에 못 할 때, 선행 컬럼 값의 종류가 적다면 IN-List로 바꿔 각 값마다 = 조건으로 탐색하게 할 수 있다.

-- 인덱스: 고객_X1 (고객등급, 가입일자)
-- 고객등급 종류: 'A', 'B', 'C' 세 가지

-- 변경 전: 고객등급 범위 조건 → 가입일자는 필터
WHERE 고객등급 BETWEEN 'A' AND 'C'
AND   가입일자 BETWEEN '20240101' AND '20240131'

-- 변경 후: 각 등급별로 가입일자 범위만 스캔
WHERE 고객등급 IN ('A', 'B', 'C')
AND   가입일자 BETWEEN '20240101' AND '20240131'

|  1 |  INLIST ITERATOR                     |        |
|  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| 고객    |
|* 3 |    INDEX RANGE SCAN                  | 고객_X1 |
Predicate Information
   3 - access(("고객등급"='A' OR "고객등급"='B' OR "고객등급"='C')
              AND "가입일자">='20240101' AND "가입일자"<='20240131')

• IN 조건이 인덱스 액세스 조건으로 쓰이려면 IN-List Iterator 방식으로 풀려야 한다. 그렇지 않으면 IN 조건은 필터로 처리된다. 힌트 NUM_INDEX_KEYS(테이블 인덱스 n)으로 액세스 조건에 쓸 인덱스 컬럼 수를 조정할 수 있다.
• 선행 컬럼에 조건이 아예 없다면 Index Skip Scan을 고려할 수 있다. 자세한 내용은 인덱스 스캔 방식을 참고한다.

기타 비효율 패턴

LIKE로 BETWEEN 대체하는 경우

-- 인덱스: (판매월, 판매구분)
WHERE 판매월 LIKE '2024%' AND 판매구분 = 'A'      -- 판매월이 범위 조건 → 판매구분 필터
WHERE 판매월 BETWEEN '202401' AND '202412' AND 판매구분 = 'A'

LIKE와 BETWEEN 모두 범위 검색 조건이지만, LIKE '2024%'는 '2024'로 시작하는 모든 값('2024', '2024X' 등)을 포함하므로 BETWEEN보다 스캔 범위가 같거나 넓다. 범위 시작·끝이 명확하면 BETWEEN이 유리하다.

범위 조건을 남용하는 경우

옵션 조건 처리를 위해 = 대신 LIKE :val || '%'나 BETWEEN을 쓰면, 원래 =로 처리될 수 있던 컬럼이 범위 조건이 되어 뒤 컬럼의 스캔 범위 축소 효과를 잃는다.

-- 인덱스: (회사, 지역, 상품명)
-- 지역을 선택 입력으로 처리하려고 LIKE 사용 → 지역이 범위 조건이 되어 상품명이 필터화
WHERE 회사 = :com AND 지역 LIKE :reg || '%' AND 상품명 LIKE :prod || '%'

이 경우 지역 조건이 입력된 경우와 아닌 경우를 UNION ALL로 분기하거나, 필수 조건은 =로 처리하는 것이 좋다.

결합 인덱스 컬럼 순서 결정 기준

시험 포인트: 결합 인덱스 컬럼 순서는 "선택도가 낮은 컬럼 우선"이 아니라 "항상 사용되는 컬럼, = 조건 컬럼 우선, 범위 조건은 뒤로"가 핵심이다. 모든 조건이 =면 순서는 스캔 효율에 영향이 없다.

소트 연산을 생략할 수 있는 인덱스

인덱스는 정렬되어 있으므로 ORDER BY, GROUP BY, MIN/MAX를 별도 소트 없이 처리할 수 있다. 부분범위 처리가 가능해져 Top-N 쿼리 응답이 빨라진다.

CREATE INDEX 주문_X2 ON 주문 (고객번호, 주문일자);

SELECT * FROM 주문
WHERE  고객번호 = 'C01'
AND    주문일자 >= '20240101'
ORDER BY 주문일자 DESC;

| Id | Operation                    | Name   |
|  1 |  TABLE ACCESS BY INDEX ROWID | 주문    |
|* 2 |   INDEX RANGE SCAN DESCENDING| 주문_X2 |

SORT ORDER BY 오퍼레이션이 없다. 소트를 생략하려면 다음 조건을 만족해야 한다.

소트 튜닝 전반은 소트 튜닝, Top-N 처리는 Top N 쿼리를 참고한다.

인덱스 설계 시 고려사항

핵심 요약

• 결합 인덱스는 선두 컬럼부터 차례로 정렬되며, 첫 범위 조건까지만 스캔 범위를 줄이고 그 뒤 조건은 필터 역할만 한다.
• 인덱스 액세스 조건은 스캔 범위를, 인덱스 필터 조건은 테이블 액세스 여부를, 테이블 필터 조건은 최종 결과를 결정한다.
• = 조건 컬럼을 앞에, 범위 조건 컬럼을 뒤에 두면 스캔 범위가 최소화된다.
• 선행 컬럼 범위 조건을 IN-List로 바꾸면 후행 컬럼도 액세스 조건이 되지만, IN 값이 많으면 수직적 탐색 반복으로 역효과가 난다.
• 범위가 명확하면 LIKE보다 BETWEEN이 유리하고, 옵션 조건에 LIKE·BETWEEN을 남용하면 후행 컬럼이 필터화된다.
• 결합 인덱스 컬럼 순서는 항상 사용되는 컬럼, = 조건 컬럼, 소트 생략, 선택도 순으로 고려한다. 모든 조건이 =면 순서는 스캔 효율과 무관하다.
• = 조건 선행 컬럼 다음에 ORDER BY 컬럼이 같은 순서·방향으로 오면 소트 연산을 생략할 수 있다.