재무팀이 1년치를 고르자 「기간 매출」 조회가 33만 블록을 읽는다

기초SELECT한빛유통78 / 700

상황

정산 화면의 「기간 매출」 조회는 조건을 고친 뒤 인덱스를 탈 수 있게 되었다. 그러자 개발자가 "앞으로도 꼭 인덱스를 타게" 하겠다며 INDEX 힌트를 넣었다. 하루나 이틀 조회는 빨라졌다(하루 919블록). 연말에 재무팀이 1년치를 고르자 화면이 한참 돌아간다. 아래는 2024년 1년치(:v_from = '20240101', :v_to = '20241231')를 조회했을 때다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · ORD_DT 하루 913건(전부 자정) · 2024년 333,245건(33.3%)
인덱스 IX_ORD_BIG_DT (ORD_DT): BLEVEL 2 · LEAF_BLOCKS 2,653 · CLUSTERING_FACTOR 1,000,000 · 통계 최신(교재 04화 실측)
이 문제에는 커버링 인덱스 (ORD_DT, ORD_AMT)가 없다

원본 SQL

sql

SELECT /*+ INDEX(o IX_ORD_BIG_DT) */
       COUNT(*), SUM(o.ORD_AMT)
  FROM T_ORD_BIG o
 WHERE o.ORD_DT >= TO_DATE(:v_from, 'YYYYMMDD')
   AND o.ORD_DT <  TO_DATE(:v_to, 'YYYYMMDD') + 1

실행계획 학습용 예시 · Buffers는 교재 04화 실측

xplan

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |               |      1 |        |      1 |00:00:00.36 |    334K|
|   1 |  SORT AGGREGATE                       |               |      1 |      1 |      1 |00:00:00.36 |    334K|
|*  2 |   FILTER                              |               |      1 |        |    333K|00:00:00.33 |    334K|
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T_ORD_BIG     |      1 |    334K|    333K|00:00:00.31 |    334K|
|*  4 |     INDEX RANGE SCAN                  | IX_ORD_BIG_DT |      1 |    334K|    333K|00:00:00.04 |     886 |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(TO_DATE(:V_TO,'YYYYMMDD')+1>TO_DATE(:V_FROM,'YYYYMMDD'))
   4 - access("O"."ORD_DT">=TO_DATE(:V_FROM,'YYYYMMDD') AND "O"."ORD_DT"<TO_DATE(:V_TO,'YYYYMMDD')+1)

(0번 줄 Buffers 정확한 값 334,132 · 같은 조건 FULL 강제 5,826)

문제

Buffers 334,132가 어디서 나오는지 IX_ORD_BIG_DT의 BLEVEL, LEAF_BLOCKS, CLUSTERING_FACTOR로 분해하여 설명하시오. FULL이 5,826블록으로 끝나는 이유와 견주어 설명하시오.
SQL을 고치시오. 이 테이블에서 이 인덱스가 FULL보다 싼 범위는 대략 며칠까지인지 구하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 힌트를 뗀다 — 범위가 짧으면 인덱스, 길면 FULL 을 옵티마이저가 고른다
SELECT COUNT(*), SUM(o.ORD_AMT)
  FROM T_ORD_BIG o
 WHERE o.ORD_DT >= TO_DATE(:v_from, 'YYYYMMDD')
   AND o.ORD_DT <  TO_DATE(:v_to, 'YYYYMMDD') + 1;

-- 어느 범위든 싸게 하려면 (T2 승인 · 적재 비용과 함께) 집계 컬럼까지 담은 커버링
-- CREATE INDEX IX_ORD_BIG_DT_AMT ON T_ORD_BIG (ORD_DT, ORD_AMT) INVISIBLE;

개선 후 실행계획 학습용 예시 · Buffers는 교재 04화 실측

xplan

-------------------------------------------------------------------------------------------
| Id  | Operation           | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |      1 |        |      1 |00:00:00.06 |    5826 |
|   1 |  SORT AGGREGATE     |           |      1 |      1 |      1 |00:00:00.06 |    5826 |
|*  2 |   FILTER            |           |      1 |        |    333K|00:00:00.05 |    5826 |
|*  3 |    TABLE ACCESS FULL| T_ORD_BIG |      1 |    334K|    333K|00:00:00.04 |    5826 |
-------------------------------------------------------------------------------------------
   2 - filter(TO_DATE(:V_TO,'YYYYMMDD')+1>TO_DATE(:V_FROM,'YYYYMMDD'))
   3 - filter(("O"."ORD_DT">=TO_DATE(:V_FROM,'YYYYMMDD') AND "O"."ORD_DT"<TO_DATE(:V_TO,'YYYYMMDD')+1))

모범 답안 서술

4번 줄(인덱스)은 886블록뿐이다. 나머지 33만 블록은 3번 줄 TABLE ACCESS BY INDEX ROWID에서 나온다. 선택도 s = 333,245 / 1,000,000 = 33.3%로 분해하면 다음과 같다. 수직 탐색 BLEVEL 2 + 수평 탐색 LEAF 2,653 × s ≈ 884 + 테이블 랜덤 액세스 CF 1,000,000 × s ≈ 333,245 = 약 334,131(실측 334,132). CF가 행수와 같다는 것은 행마다 다른 블록으로 간다는 뜻이다. 그래서 33만 행을 찾으려고 테이블 블록을 33만 번, 한 블록씩 찾아간다. FULL은 같은 테이블의 5,826블록을 한 번씩, 한 번에 여러 블록(멀티블록)으로 읽는다. 33만 행은 모두 그 5,826블록 안에 있으므로, 한 블록을 57번 따로 찾아가는 대신 한 번만 읽는 셈이다. 힌트를 뗀다. 그러면 옵티마이저가 범위를 보고 1년치에는 FULL을 고른다(334,132 → 5,826). 짧은 범위에서는 스스로 인덱스를 고른다. 손익분기는 (LEAF + CF) × s = 테이블 블록으로 푼다. (2,653 + 1,000,000) × s = 5,826 → s ≈ 0.58%이므로, 이 테이블에서는 6일까지 인덱스가 이기고 7일부터 진다(교재 04화 곡선: 6일 5,496, 7일 6,411). 화면에서 기간을 고르는 SQL에 힌트로 경로를 고정하면 안 되는 이유가 여기에 있다. 어느 범위든 싸게 하려면 ORD_AMT까지 담은 커버링 인덱스 (ORD_DT, ORD_AMT)를 적재 비용과 함께 T2에 올린다. 테이블에 가지 않으니 CF 항이 없어서 1년치도 3,227블록(FAST FULL SCAN)이면 된다.

해설

인덱스가 늘 빠른 것이 아니라 적게 읽을 때 빠르다. 손익분기가 5%나 10%라는 식으로 책에 정해진 숫자는 없고, 그 테이블의 CF가 정한다(같은 100만 행이라도 버거 ORD는 85%).
블록 수로는 57배지만 시간으로는 차이가 더 벌어질 수 있다. 버퍼 캐시에 없으면 인덱스 경로는 33만 번의 싱글블록 I/O를 하고, FULL은 수십 번의 멀티블록 I/O로 끝나기 때문이다(교재 04화 §2).
힌트는 그때의 범위와 규모를 보고 내린 판단이다(JOIN-001의 USE_NL과 같은 문제). 꼭 고정해야 한다면 범위별로 SQL을 나누거나, 힌트 대장에 '며칠까지'를 적어 둔다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · Buffers는 교재 04화 실측 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · Buffers는 교재 04화 실측 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황