SQLP 튜닝 연습장

테마

02화 · 파고들기 ​

필요할 때 찾아보는 문서다. 처음 읽을 때는 1_사건.md만으로 충분하다.

찾는 것바로 가기
B*Tree는 어떻게 생겼나§1 구조
스캔 방식 6종 구분§2 스캔
인덱스 무력화 6종 + 교정형§3 교정표
☠️ 날짜 범위: 고치다가 답이 틀어지는 자리§3.5 날짜
커버링 인덱스§4 커버링
클러스터링 팩터 (맛보기)§5 CF
측정값 전체§6 측정
공식문서 좌표§7 좌표

1. B*Tree — 정렬된 목록 ​

인덱스는 빨리 찾는 마법이 아니라 정렬된 목록이다. 정렬되어 있으므로 "여기부터 여기까지"를 지목할 수 있고, 그래서 나머지는 읽지 않아도 된다.

                  [루트]                        ← blevel 2 면 루트+브랜치 2단
                 /      \
         [브랜치]        [브랜치]
         /     \          /     \
    [리프]──[리프]──[리프]──[리프]              ← 리프끼리 양방향으로 이어져 있다
     │        │
   (키값, ROWID)                                 ← 키는 정렬, ROWID 는 테이블 위치
용어뜻02화 실측 (IX_ORD_BIG_DT)
BLEVEL루트에서 리프까지 내려가는 깊이2
LEAF_BLOCKS리프 블록 수 = 인덱스의 실제 크기2,653 (테이블은 5,923)
NUM_ROWS인덱스에 들어 있는 키 개수1,000,000

탐색은 두 단계로 나뉜다.

  1. 수직 탐색: 루트에서 리프까지 BLEVEL만큼 내려간다. 100만 건이어도 3~4블록이면 리프에 닿는다.
  2. 수평 탐색: 리프를 옆으로 따라가며 훑는다. 범위가 넓을수록 길어진다.

02화에서 INDEX RANGE SCAN이 읽은 76블록도 이렇게 나뉜다. 수직 탐색 3~4블록에 수평 탐색 70여 블록이다.

⚠️ NULL은 저장되지 않는다 ​

B*Tree는 인덱스 키가 전부 NULL인 행을 아예 저장하지 않는다. 그래서 WHERE city IS NULL은 인덱스로 찾을 수 없다. 실측에서도 TABLE ACCESS FULL이 나왔다.

단일 컬럼 인덱스라면 "그 컬럼이 NULL인 행"이 통째로 빠진다. 복합 인덱스는 키 컬럼 중 하나라도 NULL이 아니면 저장된다. 그래서 (col, 0)처럼 상수를 끼워 넣어 NULL인 행까지 인덱스에 담는 기법이 있다.

2. 인덱스 스캔 6종 ​

이름이 비슷해서 헷갈리지만, "찾아가는가"와 "훑는가"로 나눠 보면 정리된다.

스캔성격언제 나오나02화에서 본 것
INDEX UNIQUE SCAN찾아감 · 1건PK/UK 등치 조건—
INDEX RANGE SCAN찾아감 · 범위선두 컬럼에 범위·등치 조건✅ 76 / 91블록
INDEX FULL SCAN훑음 · 정렬 순서 유지ORDER BY를 인덱스로 대체—
INDEX FAST FULL SCAN훑음 · 순서 무시, 멀티블록인덱스 전체를 읽어야 하지만 순서는 필요 없을 때✅ 56 / 3,227블록
INDEX SKIP SCAN찾아감 · 반복선두 컬럼의 값 종류가 적을 때 건너뛰며—
INDEX RANGE SCAN DESCENDING찾아감 · 역순ORDER BY ... DESC—

📌 "인덱스를 못 탄다"는 말이 부정확한 이유 ​

선두 %LIKE, 부정형, 선두 컬럼 누락에서도 인덱스는 쓰였다. 다만 RANGE SCAN(찾아가기)이 아니라 FAST FULL SCAN(전부 훑기)이었다. 인덱스가 테이블보다 작으니 테이블 전체 스캔보다는 쌀 수 있다. 정확히 말하면 "찾아갈 수 없어서 훑는다"가 맞다.

실측 비교(같은 조건, CUSTOMERS.CITY):

조건스캔Buffers
city = 'SEOUL'INDEX RANGE SCAN10
city LIKE '%OUL'INDEX FAST FULL SCAN56
UPPER(city) = 'SEOUL'TABLE ACCESS FULL126

3. Sargable 교정표 — 무력화 6종 ​

Sargable은 Search ARGument able의 줄임말로, "검색 인자로 쓸 수 있는" 조건이라는 뜻이다. 판정 기준은 하나다.

컬럼을 가공하지 않은 채로, 인덱스의 정렬 순서에서 범위를 지목할 수 있는가?

아래 값은 모두 실측값이다. ①은 T_ORD_BIG, ②~⑤는 CUSTOMERS.CITY(20,000행 · CUST_X_CITY), ⑥은 T_ORD_BIG의 복합 인덱스에서 쟀다.

#무력화 패턴결과교정형결과
①컬럼 가공
TO_CHAR(ORD_DT,'YYYYMMDD') BETWEEN …
UPPER(city) = 'SEOUL'		FULL
5,826 / 126		ORD_DT >= … AND ORD_DT < …
city = 'SEOUL'		RANGE SCAN
91 / 10
②묵시적 형변환
문자 컬럼과 숫자 리터럴을 비교하면 오라클이 컬럼 쪽에 TO_NUMBER를 씌운다		컬럼 가공과 같다리터럴을 컬럼 타입에 맞춘다
order_date = DATE '2024-06-01'		RANGE SCAN
4
③선두 % LIKE
city LIKE '%OUL'		FAST FULL SCAN
56		%를 뒤쪽으로
city LIKE 'SE%'		RANGE SCAN
10
④부정형
city != 'SEOUL'		FAST FULL SCAN
56		긍정형 IN으로 뒤집는다
(애초에 범위가 커서 FULL이 옳을 수도 있다)		—
⑤IS NULL
city IS NULL		FULL
126		NOT NULL 제약 + 기본값, 또는 (col, 0) 복합 인덱스—
⑥선두 컬럼 누락
인덱스 (ORD_DT, ORD_AMT)에 WHERE ORD_AMT = 50000		FAST FULL SCAN
3,227		선두 컬럼에도 조건을 주거나, 그 컬럼을 선두로 둔 인덱스를 만든다—

② 묵시적 형변환 — 어느 쪽이 변환되는지가 핵심이다 ​

sql
-- 멀쩡하다: 리터럴이 변환된다. 컬럼은 그대로
WHERE order_date = DATE '2024-06-01'        -- INDEX RANGE SCAN, 4 블록

-- 망가진다: 컬럼이 변환된다
WHERE varchar_col = 12345                   -- 내부적으로 TO_NUMBER(varchar_col) = 12345

규칙: 문자와 숫자를 비교하면 오라클은 문자 쪽을 숫자로 바꾼다. 그 문자가 컬럼이면 컬럼을 가공한 셈이 되어 인덱스가 무력화된다. 게다가 숫자가 아닌 값이 섞여 있으면 ORA-01722로 쿼리가 실패한다. 느려지는 정도가 아니라 장애다.

교정이 불가능할 때 — 함수 기반 인덱스 ​

대소문자를 무시하고 검색할 때처럼 컬럼 가공을 없앨 수 없는 경우가 있다. 그럴 때는 가공된 값 자체를 인덱싱한다.

sql
CREATE INDEX IX_CUST_CITY_UP ON CUSTOMERS(UPPER(city));     -- 함수 기반 인덱스(FBI)
-- 이후 WHERE UPPER(city) = 'SEOUL' 이 RANGE SCAN 을 탄다

⚠️ 대가가 있다. ⓐ 인덱스를 만든 함수 형태와 정확히 일치하는 조건에서만 탄다. ⓑ 인덱스가 하나 더 늘어나 DML이 느려진다. ⓒ 함수가 DETERMINISTIC이어야 한다. 먼저 SQL을 고칠 수 있는지 보고, 정말 안 될 때만 FBI를 쓴다.

3.5 날짜 범위 조건 — 고치다가 답이 틀어지는 자리 ​

컬럼 가공을 없애다가 가장 흔히 내는 사고다.

sql
-- ☠️ 빠르지만 틀릴 수 있다
WHERE ORD_DT BETWEEN DATE '2024-06-01' AND DATE '2024-06-30'

-- ✅ 올바름
WHERE ORD_DT >= DATE '2024-06-01' AND ORD_DT < DATE '2024-07-01'

BETWEEN의 끝값은 6월 30일 00:00:00이다. 그래서 그날 00:00 이후에 들어온 행은 전부 빠진다.

실측 — 같은 코드, 다른 테이블 ​

테이블시분초 있는 행>= AND <BETWEEN차이
T_ORD_BIG0 / 1,000,00027,39027,390없음 ← 버그가 숨는다
T_ORD99,999 / 100,0004,1103,973137건 / 34,159,700원

📌 이 표가 02화에서 가장 중요하다 ​

T_ORD_BIG에서는 테스트가 통과한다. 코드가 옳아서가 아니라 데이터가 전부 자정이라서다. 같은 코드를 T_ORD로 복사하는 순간 정산에서 3,416만 원이 사라진다.

반면 강도현의 TO_CHAR 원본은 T_ORD에서도 4,110건으로 정확하다. 강도현의 코드는 느릴 뿐 답은 맞다. BETWEEN으로 고친 코드는 빠르지만 틀린다.

느린 코드를 빠르게 만드는 것이 튜닝이고, 맞는 코드를 틀리게 만드는 것은 장애다.

규칙 ​

날짜 범위에 BETWEEN을 쓰지 않는다끝 경계가 자정으로 잘린다
>= 시작 AND < 다음 구간 시작시분초가 있든 없든 안전하다
TRUNC(ORD_DT) = …로 피해 가지 않는다그것도 결국 컬럼 가공이다(§3 ①)
성능을 고쳤으면 경계값으로 회귀 검증한다"이 테이블에서 결과가 같다"가 "맞다"는 뜻은 아니다

확인 쿼리:

sql
SELECT COUNT(*) FROM <테이블> WHERE <날짜컬럼> <> TRUNC(<날짜컬럼>);   -- 0이 아니면 시분초가 있다

4. 커버링 인덱스 ​

쿼리에 필요한 컬럼이 전부 인덱스에 있으면 테이블을 읽으러 갈 이유가 없다.

sql
SELECT COUNT(*), SUM(ORD_AMT) FROM T_ORD_BIG
 WHERE ORD_DT >= DATE '2024-06-01' AND ORD_DT < DATE '2024-07-01';

이 쿼리가 쓰는 컬럼은 ORD_DT(조건)와 ORD_AMT(집계) 둘뿐이다.

인덱스계획Buffers
없음TABLE ACCESS FULL5,827
(ORD_DT)TABLE ACCESS FULL (옵티마이저가 안 씀)5,827
(ORD_DT) + 힌트 강제INDEX RANGE SCAN → TABLE ACCESS BY INDEX ROWID27,466
(ORD_DT, ORD_AMT)INDEX RANGE SCAN만91

실행계획에서 TABLE ACCESS BY INDEX ROWID 줄이 사라졌는지를 보면 커버링 여부를 알 수 있다.

대가 ​

대가어디서 청구되나
인덱스가 커진다(LEAF_BLOCKS 2,653 → 3,206)저장 공간
DML마다 인덱스를 갱신해야 한다14화: 야간 배치가 아침을 넘긴다
컬럼을 더 넣을수록 커버 범위는 넓어지지만 효율은 떨어진다설계 판단

선배의 규칙: 커버링 인덱스는 그 쿼리가 자주 돌고, 필요한 컬럼이 적고, 그 테이블에 DML이 드물 때 쓴다. 세 조건 중 하나라도 어긋나면 비용이 이득보다 커질 수 있다.

5. 클러스터링 팩터 — 04화 예고 ​

여기서는 "27,466이 왜 나왔는가"만 설명한다. 본격적인 이야기는 04화에서 한다.

sql
SELECT index_name, clustering_factor, num_rows, leaf_blocks
  FROM user_indexes WHERE table_name = 'T_ORD_BIG';
INDEX_NAME          CLUSTERING_FACTOR    NUM_ROWS   LEAF_BLOCKS
------------------ ------------------ ----------- -------------
IX_ORD_BIG_DT              1,000,000   1,000,000          2,653

CF(클러스터링 팩터)는 인덱스 순서대로 읽어 갈 때 테이블 블록을 몇 번 바꿔 타는지 센 값이다.

CF 값의미
≈ 테이블 블록 수 (5,923)최선. 인덱스 순서와 저장 순서가 비슷하다
≈ 행수 (1,000,000)최악. 행마다 블록이 바뀐다. 캐리어 하나에 한 잔씩 담긴 꼴이다

데이터가 날짜순으로 쌓이지 않아서 T_ORD_BIG은 최악 쪽에 해당한다.

인덱스를 거쳐 읽을 때의 비용 근사식

비용 ≈ BLEVEL + (LEAF_BLOCKS × 선택도) + (CLUSTERING_FACTOR × 선택도)
       └ 수직 ┘   └──── 수평 ────┘     └──── 테이블 랜덤 액세스 ────┘
                                          ← 여기가 전부를 잡아먹는다

선택도 2.74%를 넣으면 2 + 73 + 27,400 ≈ 27,475가 나온다. 실측값 27,466과 거의 같다.

커버링 인덱스로 극적인 차이가 나는 것도 세 번째 항이 통째로 사라지기 때문이다.

6. 이 화의 측정값 전부 ​

모두 ora-hanorder에서 잰 값이다(2026-10-18).

Q1 리비전별 ​

revSQL인덱스계획BuffersE-Rows
rev00TO_CHAR(ORD_DT,…) BETWEEN없음TABLE ACCESS FULL5,8262,500
rev00〃(ORD_DT)TABLE ACCESS FULL5,8262,500
rev00〃 + INDEX 힌트(ORD_DT)TABLE ACCESS FULL (힌트 무시)5,8262,500
rev01ORD_DT >= … AND < …(ORD_DT)TABLE ACCESS FULL5,82728,336
rev01〃 + INDEX 힌트(ORD_DT)INDEX RANGE SCAN + ROWID27,46628,336
rev02ORD_DT >= … AND < …(ORD_DT, ORD_AMT)INDEX RANGE SCAN만9128,336

실제 결과는 27,390행으로, 전체 1,000,000행의 2.74%다.

무력화 6종 ​

조건스캔BuffersA-Rows
UPPER(city)='SEOUL'TABLE ACCESS FULL1263,333
city='SEOUL'INDEX RANGE SCAN103,333
city LIKE '%OUL'INDEX FAST FULL SCAN563,333
city LIKE 'SE%'INDEX RANGE SCAN103,333
city != 'SEOUL'INDEX FAST FULL SCAN5616,667
city IS NULLTABLE ACCESS FULL1260
ORD_AMT=50000 (선두 누락)INDEX FAST FULL SCAN3,227223
order_date = DATE '2024-06-01'INDEX RANGE SCAN4274

UPPER(city)의 E-Rows는 200인데 실제는 3,333이다. 컬럼을 가공하면 추정도 함께 망가진다는 두 번째 증거다.

7. 원천 좌표 ​

전체 지도 → 00_자료준비/06_원서와_공식문서_지도.md PDF 페이지 = 책 페이지 − 2 · 성능고도화II_목차색인.md (공개 사이트에는 없음)

성능고도화 (주 원천) ​

주제권-장-절
인덱스 기본 원리 · B*Tree 수직/수평 탐색II권 Ch1 01절
인덱스 기본 사용법 · 스캔 6종II권 Ch1 02절
인덱스 사용이 불가능한 경우 (무력화)II권 Ch1 03절
함수 기반 인덱스 · 가상 컬럼II권 Ch1 04절
클러스터링 팩터II권 Ch1 05절 (→ 04화)

오라클 공식문서 ​

주제매뉴얼장 제목
인덱스 선택 기준tgsqlOptimizer Access Paths
B*Tree 구조cncptIndexes and Index-Organized Tables
함수 기반 인덱스adminManaging Indexes
힌트 문법과 무시되는 조건tgsqlInfluencing the Optimizer

이 화에서 꼭 확인할 뷰 ​

sql
USER_INDEXES        -- status · blevel · leaf_blocks · clustering_factor · num_rows
USER_IND_COLUMNS    -- 인덱스가 어느 컬럼을 어느 순서로 쓰는가 (선두 컬럼 확인)
USER_IND_EXPRESSIONS -- 함수 기반 인덱스의 식

다음 화가 여기서 가져가는 것 ​

화가져가는 것
03Q1_rev02의 날짜 조건. 고칠 수 없는 경우를 다룬다. ora-dist에는 날짜가 VARCHAR2(8)로 박혀 있다
04CF = 1,000,000과 손익분기점. 오늘 만든 인덱스 두 개로 범위를 넓혀 가며 잰다
10E-Rows가 정확해졌는데도 틀리는 경우 (히스토그램)
14인덱스 2개의 적재 비용 청구서