1. 왜 검증하게 됐나
'친절한 SQL 튜닝'을 읽으며 = 조건으로 묶인 컬럼끼리는 인덱스 순서를 바꿔도 I/O에 영향을 주지 않는다는 내용을 접했다. 해당 내용은 '헷갈리는 부분 정리' 카테고리에 발행해 두었다.
🔗 복합 인덱스에서 = 조건 컬럼의 순서는 왜 상관없을까?
soo-archiving.tistory.com/20
이론으로는 이해했지만 머리로 아는 것과 눈으로 보는 건 다르다고 생각해서 직접 가상의 데이터를 만들고 실행계획을 떠서 정말 그런지 확인해보기로 했다.
2. 검증 환경 만들기
노트북에 Orange툴이 있었지만 사용 기간이 한달짜리 트라이얼 버전이라 나중에도 지속적으로 쓸 환경이 필요했다. 그래서 Docker로 Oracle을 띄우고 SQL Developer로 접속하는 방식을 택했다. CMD에서 도커 컨테이너로 나만의 테스트용 DB 서버를 만들고, SQL Developer로 검증 환경을 구성했다.
이 과정에서 알게 된 게 CDB와 PDB의 차이다. 오라클은 12c부터 멀티테넌트 구조로 바뀌었는데, 핵심은 DB가 2층 구조가 됐다는 것이다.
- CDB (Container Database) : 전체를 감싸는 관리용 껍데기. 시스템 메타데이터, 공통 사용자 등 운영 뼈대가 있다.
- PDB (Pluggable Database) : 그 안의 실제 작업 공간. 이름처럼 꽂았다 뺐다 할 수 있는 독립된 DB다.
멀티테넌트의 장점은 하나의 CDB 안에 여러 PDB(운영용, 테스트용 등)를 두고 관리할 수 있다는 것이다. PDB는 통째로 다른 CDB로 옮기는 것도 가능하다. 그래서 나는 PDB(XEPDB1)에 접속해 그 안에 테스트 데이터를 만들었다.
먼저 100만 건의 더미 데이터를 생성했다. 이때 데이터를 한쪽에 몰리지 않게 고르게 흩어놓아야 실행계획이 왜곡되지 않고 의미 있는 검증이 된다.
INSERT INTO trade_history
SELECT
CHR(65 + MOD(LEVEL, 5)), -- 고객등급: A~E
TRUNC(DBMS_RANDOM.VALUE(1, 10000)), -- 고객번호
DATE '2024-01-01' + MOD(LEVEL, 365), -- 거래일자
'TYPE' || MOD(LEVEL, 10),
TRUNC(DBMS_RANDOM.VALUE(1, 1000))
FROM DUAL
CONNECT BY LEVEL <= 1000000;3. 인덱스 만들기
= 조건 컬럼의 순서만 다른 두 인덱스를 만들었다. 거래일자(범위 조건)는 둘 다 맨 뒤에 뒀다.
-- IDX_A: 고객등급 → 고객번호 → 거래일자
CREATE INDEX idx_a ON trade_history(cust_grade, cust_no, trade_date);
-- IDX_B: 고객번호 → 고객등급 → 거래일자 (앞 두 컬럼 순서만 바꿈)
CREATE INDEX idx_b ON trade_history(cust_no, cust_grade, trade_date);
그리고 옵티마이저가 정확히 판단하도록 **DBMS_STATS.GATHER_TABLE_STATS**로 통계를 수집했다.
4. = 조건 순서 비교 (핵심 1)
이제 가장 궁금했던 실행계획을 떠봤다. idx_a 를 쓰도록 힌트를 주고 실행했다.
옵티마이저는 INDEX RANGE SCAN을 선택했다(인덱스를 제대로 탔다는 뜻이다). 예상 행 수(E-Rows)는 20, 실제(A-Rows)는 24로 큰 차이가 없었다. 읽은 블록수(논리적읽기)를 보여주는 Buffers는 인덱스 스캔에서 3으로 매우 양호했고 테이블 액세스까지 합한 총 Buffers는 27이었다. 그리고 컬럼 순서만 바꾼 idx_b로도 똑같이 떠봤다.
결과는 완전히 동일했다! Buffers 3, 총 27. 순서를 바꿨는데 Buffers가 똑같았다.즉, 해당 인덱스들이 =조건이 선두에 연속으로 온다면 컬럼의 순서와 관계없이 동일한 실행 결과가 나온다는 걸 직접 확인 한 것이다. 책의 이론이 실제로 맞았다.
5. 범위 조건을 가운데 넣으면? (핵심 2)
여기서 궁금증이 하나 더 생겼다. idx_a와 b는 범위 조건(거래일자)을 맨 뒤에 뒀는데 이걸 가운데 끼우면 어떻게 될까??
-- IDX_C: 범위 조건(거래일자)을 가운데 배치
CREATE INDEX idx_c ON trade_history(cust_grade, trade_date, cust_no);
옵티마이저는 INDEX RANGE SCAN이 아닌 INDEX SKIP SCAN을 선택했다. 스킵 스캔은 선두 컬럼의 카디널리티가 낮아서 값들을 건너뛰며 여러 번 나눠 탐색하는 방식인데 이 인덱스 구조가 쿼리에 안 맞아서 옵티마이저가 어쩔 수 없이 우회 전략을 쓴다는 신호가 된다. 즉 인덱스 설계 순서가 좋지 않다는 증거인 셈이다
수치를 보면 차이가 분명히 보인다. 예상 행(20)과 실제 행(24)은 idx_a, idx_b와 같지만, Buffers가 83, 테이블 액세스까지 총 107까지 치솟았다. 같은 24건을 얻는데 107번의 논리적읽기가 발생한 것이다. idx_a, idx_b의 27과 비교하면 약 4배 비효율적이다.
원인은 Predicate Information에 그대로 드러난다. 범위 조건 뒤로 밀린 cust_no가 filter로 전락한 것이다. 필터 조건이 되면 인덱스로 범위를 좁히지 못하고 일단 읽은 다음 "맞냐 아니냐"를 일일이 검사한다. 그만큼 불필요한 데이터까지 모두 I/O를 거치게 되어 비용이 올라간다.
6. 정리하며
이 검증을 통해 알게 된 것은 B-Tree 인덱스는 단순한 엑셀 필터 같은 게 아니라는 점이다. 검색에 가장 효율적인 진입점을 찍어줌으로써 읽어야 할 블록수 I/O를 최대한 줄이는 것 — 그게 인덱스 설계의 핵심이다. 그리고 인덱스 설계에서 중요한 두 가지를 직접 확인할 수 있었다.
- = 조건 컬럼끼리는 순서를 고민할 필요가 없다. 어느 순서든 단일 진입점을 만들기 때문이다.
- 범위 조건은 반드시 맨 뒤에 둬야 한다. 왜냐하면 원래 = 조건이던 컬럼도 범위 조건 뒤에 놓이면 필터로 전락하기 때문이다. 가운데 두면 옵티마이저가 RANGE SCAN을 포기하고 SKIP SCAN으로 빠지면서 I/O가 폭증하게 된다.
책으로 읽을땐 머리로만 이해했던 내용을 직접 100만 건으로 떠보니 Buffers 숫자같은 통계수치가 눈으로 보이며 확실히 와닿았다. 효율적인 인덱스 설계가 왜 중요한지 체감한 검증이었다.