월 판매번호 목록이 지난주부터 477블록에서 1,757블록을 읽는다

중급SELECT유통116 / 700

상황

유통 본사의 「월 판매번호 목록」은 회계 연동용으로 한 달 치 판매번호와 판매일자를 내려준다. 지난주 외부 점검 보고서에 이런 줄이 있었다. "버거원에서 INDEX_JOIN으로 테이블 액세스를 없애 블록을 4분의 1로 줄였다. 같은 모양의 SQL에 적용을 권고한다." 조민우가 권고대로 힌트를 붙였는데, 이 SQL은 오히려 무거워졌다. 아래는 2023년 6월을 골랐을 때 힌트를 붙인 뒤와 붙이기 전이다.

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록(블록당 약 109행) · 판매 날짜순으로 적재 · 2023년 6월 20,953건(교재 03화 실측) · 컬럼은 모두 NOT NULL
인덱스 CF10_PK (SLE_NO): BLEVEL 2 · 리프 약 1,680블록 · IX_CF10_SLE_DT (SLE_DT): BLEVEL 2 · 리프 1,393블록 · CLUSTERING_FACTOR 9,921(교재 03화 실측) · 통계 최신
참고: 버거원 ORD_RANDOM의 날짜 인덱스는 CLUSTERING_FACTOR가 999,958이다(행 수와 같다)

원본 SQL

sql

SELECT /*+ INDEX_JOIN(a IX_CF10_SLE_DT CF10_PK) */
       a.SLE_NO, a.SLE_DT
  FROM CF10 a
 WHERE a.SLE_DT BETWEEN :v_from AND :v_to

실행계획 학습용 예시 · 6월 건수와 IX_CF10_SLE_DT의 CF는 교재 03화 실측

xplan

[힌트를 붙인 뒤]
---------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                  |      1 |        |  20953 |00:00:00.09 |    1757 |          |
|*  1 |  FILTER                 |                  |      1 |        |  20953 |00:00:00.09 |    1757 |          |
|*  2 |   VIEW                  | index$_join$_001 |      1 |   2094 |  20953 |00:00:00.08 |    1757 |          |
|*  3 |    HASH JOIN            |                  |      1 |        |  20953 |00:00:00.08 |    1757 | 2316K (0)|
|*  4 |     INDEX RANGE SCAN    | IX_CF10_SLE_DT   |      1 |   2094 |  20953 |00:00:00.01 |      61 |          |
|   5 |     INDEX FAST FULL SCAN| CF10_PK          |      1 |   2094 |    500K|00:00:00.05 |    1696 |          |
---------------------------------------------------------------------------------------------------------------
   1 - filter(:V_FROM<=:V_TO)
   2 - filter(("SLE_DT">=:V_FROM AND "SLE_DT"<=:V_TO))
   3 - access(ROWID=ROWID)
   4 - access("SLE_DT">=:V_FROM AND "SLE_DT"<=:V_TO)

[힌트를 붙이기 전]
---------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |      1 |        |  20953 |00:00:00.03 |     477 |
|*  1 |  FILTER                              |                |      1 |        |  20953 |00:00:00.03 |     477 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| CF10           |      1 |   2094 |  20953 |00:00:00.03 |     477 |
|*  3 |    INDEX RANGE SCAN                  | IX_CF10_SLE_DT |      1 |   2094 |  20953 |00:00:00.01 |      61 |
---------------------------------------------------------------------------------------------------------
   1 - filter(:V_FROM<=:V_TO)
   3 - access("SLE_DT">=:V_FROM AND "SLE_DT"<=:V_TO)

문제

힌트를 붙이기 전과 뒤의 실행계획을 줄 단위로 비교해, 테이블에 가지 않게 됐는데도 Buffers가 477에서 1,757로 늘어난 이유를 설명하시오.
버거원의 SQL에서는 같은 처방이 왜 효과가 있었는지 클러스터링 팩터로 설명하고, 인덱스 조인이 테이블 액세스보다 유리해지는 조건을 정리하시오.
SQL을 고치시오. 블록을 더 줄일 방법이 있다면 그 대가와 함께 제안하시오.

개선 SQL원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.

개선 사유무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자

모범답안

모범 답안 SQL

sql

SELECT a.SLE_NO, a.SLE_DT          -- INDEX_JOIN 힌트를 뗀다 (CF 가 좋은 테이블에서는 테이블 액세스가 싸다)
  FROM CF10 a
 WHERE a.SLE_DT BETWEEN :v_from AND :v_to;

-- 더 줄이려면 (T2 · 점검 시간): 기존 (SLE_DT) 를 앞부분으로 품는 인덱스로 바꿔 단다
-- CREATE INDEX IX_CF10_DT_SLE ON CF10 (SLE_DT, SLE_NO) INVISIBLE;   → 약 95 블록 · 확인 뒤 IX_CF10_SLE_DT 는 INVISIBLE → DROP

개선 후 실행계획 학습용 예시 · 6월 건수와 IX_CF10_SLE_DT의 CF는 교재 03화 실측

xplan

[힌트를 뗀 뒤 — 붙이기 전 계획으로 돌아온다]
---------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |      1 |        |  20953 |00:00:00.03 |     477 |
|*  1 |  FILTER                              |                |      1 |        |  20953 |00:00:00.03 |     477 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| CF10           |      1 |   2094 |  20953 |00:00:00.03 |     477 |
|*  3 |    INDEX RANGE SCAN                  | IX_CF10_SLE_DT |      1 |   2094 |  20953 |00:00:00.01 |      61 |
---------------------------------------------------------------------------------------------------------
   1 - filter(:V_FROM<=:V_TO)
   3 - access("SLE_DT">=:V_FROM AND "SLE_DT"<=:V_TO)

[비교 — (SLE_DT, SLE_NO) 로 바꿔 달면] INDEX RANGE SCAN 하나 · 약 95블록 (리프 약 2,170블록짜리 인덱스의 6월 구간)

모범 답안 서술

힌트를 붙이기 전에는 3번 줄 IX_CF10_SLE_DT 범위 스캔 61블록에 2번 줄 테이블 액세스 416블록, 합계 477블록이었다. 힌트를 붙이면 4번 줄 범위 스캔(61블록)은 같고, 테이블 대신 5번 줄에서 CF10_PK 전체 50만 항목을 INDEX FAST FULL SCAN으로 읽는다(1,696블록). 두 결과를 3번 줄에서 ROWID로 해시 조인한다(access(ROWID=ROWID)). 테이블에 가지 않게 된 대신, 판매번호를 얻으려고 PK 전체를 읽는 몫이 붙었다. 이 몫은 한 달을 고르든 하루를 고르든 고정이다. 그래서 477 → 1,757블록으로 늘었다. CF10은 판매 날짜순으로 쌓여 IX_CF10_SLE_DT의 CF가 9,921이다. 테이블 블록 4,583의 두 배쯤이니 좋은 편이다. 6월의 20,953행이 테이블 안에서 한데 모여 있어서, 테이블로 찾아가도 블록을 약 416번만 바꿔 탄다. 버거원의 ORD_RANDOM은 CF가 999,958로 행 수와 같아서 행마다 다른 블록으로 갔다. 한 주 9,589행에 테이블 액세스가 9,589블록이었으니, PK FFS 2,100블록을 내고 테이블을 피하는 것이 이득이었다. 정리하면 인덱스 조인은 '테이블 액세스로 바꿔 탈 블록 수(CF × 선택도)'가 '두 번째 인덱스를 FFS로 읽는 블록 수'보다 클 때만 유리하다. CF가 나쁘거나 범위가 넓을 때다. 그 조건은 SQL 모양이 아니라 테이블에 달려 있으므로, 같은 모양의 SQL이라도 테이블마다 다시 따져야 한다. INDEX_JOIN 힌트를 떼면 477블록으로 돌아간다. 더 줄이려면 IX_CF10_SLE_DT를 (SLE_DT, SLE_NO)로 바꿔 단다. 기존 인덱스를 앞부분으로 품으므로 인덱스 수가 늘지 않고, 범위만 읽어 약 95블록이 된다. 대가는 50만 행 인덱스를 다시 만드는 시간과, 판매번호까지 담아 항목이 커지는 것(리프 1,393 → 약 2,170블록)이다. 이 목록이 한 달에 몇 번 도는지 보고 정한다.

해설

외부 권고를 옮길 때는 'SQL 모양이 같은가'가 아니라 '데이터의 조건이 같은가'를 본다. 인덱스 조인, 커버링, 손익분기는 모두 그 테이블의 CF에 달려 있다(교재 04화).
힌트를 넣었는데 Buffers가 늘었다면 힌트를 먼저 의심한다. 측정 없이 붙인 힌트는 다음 사람이 이유도 모른 채 지키게 된다(교재 06화 힌트는 무엇을 하는가).
인덱스 조인의 해시 조인은 PGA를 쓴다(여기서는 약 2.3MB). 기간이 길어 범위 쪽 결과가 커지면 메모리도 커진다.
6월의 E-Rows가 2,094(실제 20,953)로 낮은 것은 SLE_DT가 문자이기 때문이다. 이 SQL에서는 경로를 바꾸지 않았지만, 다른 SQL에서는 이런 오추정이 경로를 틀어 놓는다(교재 03화).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 6월 건수와 IX_CF10_SLE_DT의 CF는 교재 03화 실측 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 6월 건수와 IX_CF10_SLE_DT의 CF는 교재 03화 실측 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황