ORACLE SQL TUNING · DEEP DIVE 1
인덱스 튜닝 심화
인덱스 액세스 비용의 구조, 결합 인덱스 설계, 랜덤 액세스 최소화, 인덱스 운영 관리까지
선수 학습 · SQL 튜닝 입문 6장, 실행계획 읽기 실습 커리큘럼 · 6~7주차 난이도 · 중급
1. 인덱스 액세스 비용의 구조
① 수직적 탐색
Root → Branch → Leaf
BLEVEL + 1 블록 (보통 3~4)
비용 거의 고정
Root → Branch → Leaf
BLEVEL + 1 블록 (보통 3~4)
비용 거의 고정
② 수평적 스캔
Leaf 블록 범위 스캔
access 조건 범위에 비례
Leaf당 수백 건 → 비교적 저렴
Leaf 블록 범위 스캔
access 조건 범위에 비례
Leaf당 수백 건 → 비교적 저렴
③ 테이블 랜덤 액세스
ROWID → 테이블 블록
건당 최대 1블록 (Single I/O)
가장 비싸다
ROWID → 테이블 블록
건당 최대 1블록 (Single I/O)
가장 비싸다
인덱스 튜닝의 두 축
① 인덱스 스캔 효율 — 수평 스캔 범위를 줄인다 (access 조건 최대화, 컬럼 순서)
② 테이블 액세스 최소화 — 테이블 방문 횟수를 줄인다 (인덱스에서 필터링, 커버링 인덱스, 클러스터링 팩터)
1.1 인덱스 손익분기점
- 테이블 전체의 일정 비율 이상을 읽으면 인덱스보다 Full Table Scan이 빠르다.
- 일반적으로 수 % 이내가 기준으로 거론되지만, 실제 손익분기점은 클러스터링 팩터에 크게 좌우된다 — 좋으면 수십 %까지도 인덱스가 유리하고, 나쁘면 1% 미만에서도 Full Scan이 낫다.
- 이유: 인덱스 랜덤 액세스는 Single Block I/O로 한 번에 1블록, Full Scan은 Multi Block I/O(+ Exadata Smart Scan, Direct Path Read)로 한 번에 다수 블록을 읽는다.
2. 인덱스 스캔 효율
2.1 access 조건 vs filter 조건
| 구분 | 인덱스 access | 인덱스 filter | 테이블 filter |
|---|---|---|---|
| 역할 | 스캔 시작점과 끝점 결정 | 스캔한 인덱스 행 중 걸러냄 | 테이블 방문 후 걸러냄 |
| 비용 | 스캔 범위 자체를 줄임 | 테이블 방문은 줄임 | 낭비 가장 큼 |
| 발생 조건 | 선두부터 연속된 = 조건 + 첫 범위 조건 | 범위 조건 뒤 컬럼, 선두 누락 컬럼 | 인덱스에 없는 컬럼 |
2.2 결합 인덱스 컬럼 순서 실험
-- 조건: STAT_CD = '10' (99%) / ORDER_DT 1일치 (0.1%) / CUST_NO = :c (0.002%) -- 인덱스 후보별 스캔 범위 -- A. (ORDER_DT, CUST_NO) → access: ORDER_DT 범위, filter: CUST_NO → 하루치 3,000건 스캔 -- B. (CUST_NO, ORDER_DT) → access: CUST_NO = AND ORDER_DT 범위 → 1~2건 스캔 -- C. (STAT_CD, ORDER_DT) → access: STAT_CD = AND ORDER_DT 범위 → 하루치 2,970건 스캔
| 원칙 | 설명 |
|---|---|
| ① = 조건 우선 | 선두에서 = 조건이 연속될수록 스캔 범위가 좁다 |
| ② 범위 조건은 뒤로 | 첫 범위 조건 이후 컬럼은 access에 기여하지 못한다 |
| ③ 항상 쓰이는 컬럼을 앞에 | 선두 컬럼이 조건에 없으면 Range Scan 불가 (Skip Scan 제외) |
| ④ 정렬·그룹핑 고려 | ORDER BY 순서와 일치하면 SORT 연산 제거 |
| ⑤ 선택도는 마지막 | "선택도 좋은 컬럼을 앞에"는 = 조건끼리일 때만 의미가 있다 |
💡 흔한 오해 — 두 컬럼이 모두 = 조건이면 순서에 따른 스캔 블록 수 차이는 거의 없다. 이때는 다른 SQL에서도 재사용될 수 있는 순서(더 자주 단독으로 쓰이는 컬럼을 선두)를 고른다.
2.3 BETWEEN을 IN으로 — 범위 조건을 = 조건으로
-- 인덱스 (ORDER_MM, CUST_NO), ORDER_MM은 'YYYYMM' 문자
-- Before: ORDER_MM 범위 조건 → CUST_NO는 filter
WHERE order_mm BETWEEN '202609' AND '202611' AND cust_no = :c
-- After: IN-List → INLIST ITERATOR, 각 월마다 (ORDER_MM =, CUST_NO =) access
WHERE order_mm IN ('202609', '202610', '202611') AND cust_no = :c
- IN 목록 개수가 적고, 범위 내 선행 컬럼 값 종류가 적을 때 효과적이다.
- 반대로 IN 목록이 길면 수직 탐색이 반복되어 오히려 불리할 수 있다.
2.4 Index Skip Scan
- 선두 컬럼 조건이 없을 때, 선두 컬럼의 Distinct 값 수가 매우 적으면 값별로 건너뛰며 탐색한다.
- 예: 인덱스
(GENDER, BIRTH_DT), 조건BIRTH_DT = :d→ GENDER 값 2개 각각에 대해 탐색 - 실행계획에 Skip Scan이 보이면 대개 인덱스 설계를 다시 볼 신호다.
3. 테이블 랜덤 액세스 최소화
3.1 클러스터링 팩터 (CF)
CF 좋음 (≈ 테이블 블록 수) CF 나쁨 (≈ 테이블 행 수)
인덱스 A A A B B B C C C 인덱스 A A A B B B C C C
│ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │
테이블 [블록1 ][블록2 ][블록3 ] 테이블 [1][7][3][9][2][5][8][4][6]
→ 3블록만 방문 → 9블록 방문 (같은 블록 재방문 포함)
| 개선 방법 | 설명 | 주의 |
|---|---|---|
| 테이블 재구성 (정렬 적재) | CREATE TABLE … AS SELECT … ORDER BY 인덱스컬럼 |
다른 인덱스의 CF는 나빠질 수 있다. 한 테이블에 CF가 모두 좋은 인덱스는 하나뿐 |
| IOT (Index-Organized Table) | 데이터를 PK 순으로 인덱스 구조에 저장 | PK 외 접근이 많으면 불리 |
| 파티셔닝 | 파티션 키 기준으로 물리적으로 모음 | |
| 커버링 인덱스 | 테이블 방문 자체를 제거 | 아래 3.2 참고 |
TABLE_CACHED_BLOCKS 통계 옵션 (12c+) |
CF 계산 시 최근 방문 블록을 캐시된 것으로 간주 | 통계 값만 보정, 실제 I/O는 그대로 |
3.2 커버링 인덱스 (Covering Index)
SQL이 필요로 하는 모든 컬럼이 인덱스에 있으면 테이블을 방문하지 않는다.
-- 조회: SELECT order_dt, order_amt FROM tb_order WHERE cust_no = :c AND order_dt >= :d CREATE INDEX ix_order_cov ON tb_order (cust_no, order_dt, order_amt); -- 실행계획: INDEX RANGE SCAN IX_ORDER_COV (TABLE ACCESS BY INDEX ROWID 단계 사라짐)
- 컬럼을 무작정 추가하면 인덱스가 커지고 DML 부하가 늘어난다. 실행 빈도가 매우 높은 핵심 SQL에 한해 적용한다.
3.3 인덱스에서 필터링 — 테이블 filter를 인덱스 filter로
-- 인덱스 (CUST_NO, ORDER_DT), 조건 CUST_NO = :c AND ORDER_DT >= :d AND STAT_CD = '90' -- STAT_CD가 인덱스에 없어 테이블 방문 후 버림 → 인덱스 끝에 STAT_CD 추가 CREATE INDEX ix_order_01 ON tb_order (cust_no, order_dt, stat_cd); -- STAT_CD는 범위 조건 뒤라 access는 아니지만 '인덱스 filter'로 테이블 방문 전에 걸러진다
3.4 BATCHED ROWID 액세스 (12c+)
TABLE ACCESS BY INDEX ROWID BATCHED— ROWID를 모아 같은 블록끼리 묶어 접근 → 랜덤 I/O 감소- 단, 결과가 인덱스 순서대로 나온다는 보장이 없다. 정렬은 반드시
ORDER BY로 명시한다. - 제어 힌트:
BATCH_TABLE_ACCESS_BY_ROWID/NO_BATCH_TABLE_ACCESS_BY_ROWID
4. 정렬·집계와 인덱스
| 패턴 | 인덱스 활용 | 실행계획 |
|---|---|---|
ORDER BY = 인덱스 순서 |
정렬 생략 | SORT 단계 없음 |
ORDER BY … DESC |
역순 스캔 | INDEX RANGE SCAN DESCENDING |
| Top-N / 페이징 | 정렬 생략 + 조기 종료 | COUNT STOPKEY, WINDOW NOSORT STOPKEY |
MIN / MAX |
인덱스 한쪽 끝만 | INDEX FULL SCAN (MIN/MAX), FIRST ROW |
COUNT(*) |
NOT NULL 컬럼 인덱스로 대체 | INDEX FAST FULL SCAN |
GROUP BY 인덱스 순서 |
정렬 기반 그룹핑 생략 | SORT GROUP BY NOSORT |
-- 고객별 최근 주문 1건 — 인덱스 (CUST_NO, ORDER_DT) SELECT MAX(order_dt) FROM tb_order WHERE cust_no = :c; -- INDEX RANGE SCAN (MIN/MAX) + FIRST ROW → 블록 3~4개만 읽음 -- 주의: MAX 대상 컬럼을 가공하면 최적화 불가 SELECT MAX(TO_CHAR(order_dt,'YYYYMMDD')) FROM tb_order WHERE cust_no = :c; -- 범위 전체 스캔
5. 특수 인덱스
| 종류 | 활용 | 예 |
|---|---|---|
| Function-Based Index | 가공 조건을 인덱스로 | CREATE INDEX ix_cust_email ON tb_customer (UPPER(email));
|
| NULL 포함 인덱스 | 단일 컬럼 B*Tree는 NULL 미저장 → 상수 추가 | CREATE INDEX ix_loan_ret ON tb_loan (return_dt, 0);
|
| 부분 인덱스 (FBI 응용) | 특정 값만 인덱싱해 크기 최소화 | CREATE INDEX ix_err ON tb_job (CASE WHEN stat_cd='E' THEN job_id END);
|
| Invisible Index | 옵티마이저에서 숨겨 영향도 테스트 | ALTER INDEX ix_x INVISIBLE; + 세션 optimizer_use_invisible_indexes=TRUE
|
| Reverse Key | RAC 우측 블록 경합 완화 | 범위 검색 불가 |
| Bitmap | DW 저선택도, 다중 조건 결합 | OLTP 금지 (DML 락 범위 큼) |
| Local Partitioned | 파티션별 독립 인덱스 | 파티션 DROP·EXCHANGE 시 관리 용이 |
⚠ 부분 인덱스 사용 시 — 조건절이 인덱스 정의와 정확히 같은 식이어야 한다. WHERE CASE WHEN stat_cd='E' THEN job_id END IS NOT NULL 처럼 작성하거나, 뷰로 감싸 제공한다.
6. 인덱스 설계 절차
- 핵심 SQL 수집 — AWR·V$SQL에서 실행 빈도·부하 상위 SQL, 신규 개발 SQL 목록
- 액세스 패턴 분석 — 테이블별로 조건 컬럼, 연산자(= / 범위 / LIKE), 정렬·그룹핑 컬럼 정리
- 조건 분포 확인 — 컬럼 NDV, 값 편중, 조회 범위
- 후보 인덱스 도출 — 여러 SQL을 최소 개수의 인덱스로 커버하도록 컬럼 순서 결정
- 기존 인덱스와 비교 — 중복(선두 컬럼 포함 관계), 미사용 인덱스 정리
- DML 영향 검토 — 인덱스 1개 추가 시 INSERT·DELETE·인덱스 컬럼 UPDATE 비용 증가
- 검증 — Invisible로 생성 → 대상 세션에서 실측 → Visible 전환
6.1 액세스 패턴 매트릭스 예
| SQL | 실행/일 | CUST_NO | ORDER_DT | STAT_CD | ORDER BY | 후보 |
|---|---|---|---|---|---|---|
| SQL-01 고객 주문 목록 | 50만 | = | 범위 | ORDER_DT DESC | (CUST_NO, ORDER_DT) | |
| SQL-02 상태별 처리 대상 | 2만 | 범위 | = | (STAT_CD, ORDER_DT) | ||
| SQL-03 고객 최근 주문일 | 10만 | = | MAX | (CUST_NO, ORDER_DT) 재사용 | ||
| SQL-04 일자별 집계 (배치) | 1 | 범위 | 파티션 Full Scan |
7. 인덱스 운영 관리
7.1 미사용 인덱스 찾기
-- 12.2+ : 인덱스 사용 추적 (기본 샘플링 방식)
SELECT owner, name, total_access_count, total_exec_count, last_used
FROM dba_index_usage
WHERE owner = 'APP'
ORDER BY total_access_count;
-- 인덱스 정의와 비교해 '한 번도 안 쓰인' 인덱스
SELECT i.owner, i.index_name, i.table_name
FROM dba_indexes i
WHERE i.owner = 'APP'
AND NOT EXISTS (SELECT 1 FROM dba_index_usage u
WHERE u.owner = i.owner AND u.name = i.index_name);
- 월말·분기 배치처럼 드물게 쓰이는 인덱스가 있으므로, 최소 한 업무 주기 이상 관찰 후 삭제한다.
- 삭제 전 INVISIBLE로 일정 기간 운영해 영향 확인 → 문제 없으면 DROP
7.2 중복 인덱스
(A)와(A, B)가 함께 있으면 대부분(A)는 불필요하다. (단,(A)가 PK/UK·FK 용도면 예외 검토)- 같은 컬럼 구성에 순서만 다른 인덱스는 각각의 SQL 패턴 근거가 있는지 확인한다.
7.3 인덱스 리빌드에 대한 오해
| 오해 | 사실 |
|---|---|
| "주기적으로 리빌드해야 성능이 유지된다" | B*Tree는 스스로 균형을 유지한다. 대부분 리빌드 효과는 일시적이다 |
| "BLEVEL이 높으면 리빌드해야 한다" | BLEVEL은 건수에 따라 자연스럽게 증가하며, 리빌드로 거의 줄지 않는다 |
| 리빌드가 효과 있는 경우 | 대량 DELETE 후 재삽입되지 않는 키 범위(예: 과거 일자)가 비어 있는 경우, 우측 증가 키에서 좌측 리프가 희소해진 경우 |
-- 공간 회수가 목적이면 온라인으로 ALTER INDEX ix_order_02 REBUILD ONLINE; -- 또는 리프 블록 병합만 (락 영향 적음) ALTER INDEX ix_order_02 COALESCE;
8. 실습 과제
과제 1. 실행계획 읽기 실습의 LAB_ORDER에서 WHERE stat_cd = '90' AND order_dt BETWEEN :d1 AND :d2 를 인덱스 (ORDER_DT), (ORDER_DT, STAT_CD), (STAT_CD, ORDER_DT) 세 가지로 실행해 Buffers를 비교하라.
기대 결과 (ORDER_DT) → 테이블 방문 후 filter로 대부분 버림 > (ORDER_DT, STAT_CD) → 인덱스 filter로 테이블 방문은 줄지만 스캔 범위는 동일 > (STAT_CD, ORDER_DT) → access 범위 자체가 1/100 수준으로 축소. Buffers가 가장 적은 것은 세 번째.
과제 2. LAB_ORDER를 CUST_NO 순으로 정렬해 재생성한 뒤, 두 인덱스의 클러스터링 팩터와 같은 SQL의 Buffers 변화를 비교하라.
기대 결과 IX_LAB_ORDER_01(CUST_NO 선두)의 CF는 테이블 블록 수에 가깝게 좋아지고, IX_LAB_ORDER_02(ORDER_DT)의 CF는 행 수에 가깝게 나빠진다. 고객 단위 조회 SQL은 빨라지고 일자 단위 조회 SQL은 느려진다 — CF는 트레이드오프다.
과제 3. 운영(또는 개발) 스키마에서 선두 컬럼이 같은 인덱스 쌍을 찾는 SQL을 작성하라.
SELECT a.table_name, a.index_name AS idx_short, b.index_name AS idx_long
FROM (SELECT table_owner, table_name, index_name,
LISTAGG(column_name, ',') WITHIN GROUP (ORDER BY column_position) AS cols
FROM dba_ind_columns WHERE table_owner = 'APP'
GROUP BY table_owner, table_name, index_name) a
JOIN (SELECT table_owner, table_name, index_name,
LISTAGG(column_name, ',') WITHIN GROUP (ORDER BY column_position) AS cols
FROM dba_ind_columns WHERE table_owner = 'APP'
GROUP BY table_owner, table_name, index_name) b
ON b.table_owner = a.table_owner AND b.table_name = a.table_name
AND b.index_name <> a.index_name
AND b.cols LIKE a.cols || ',%'
ORDER BY a.table_name;
이전 ← SQL 튜닝 입문 · 실행계획 읽기 실습 | 다음 → 조인 튜닝 심화 · AWR 리포트 분석 | 관련 → Oracle 물리 설계