인덱스 승인서의 예상 크기와 예상 블록 칸을 비워 냈다가 반려됐다

중급DDL버거145 / 700

상황

버거원 상품기획팀이 「메뉴 판매 내역」 화면을 새로 연다. 메뉴 하나와 기간(보통 한 달)을 고르면 그 메뉴가 팔린 주문을 시각순으로 보여 주고, 프로모션 효과를 볼 때는 파일로 내려받는다. 기획자 여섯 명이 하루 수백 번 열 것이라고 한다. 조민우가 (ITEM_ID, ORD_DT) 인덱스 승인서(A안)를 올렸는데 박팀장이 반려했다. "예상 크기, 높이, 화면 예상 블록, 적재 비용 칸이 비어 있다. 만들기 전에 어림해서 채워 와라. 화면이 쓰는 컬럼을 다 담는 B안 (ITEM_ID, ORD_DT, ORD_HMS, ORD_NO, ORD_ITEM_QTY)도 같은 칸으로 비교해서."

테이블과 인덱스

ORD_ITEM 주문상품: 2,472,717행 · 55,265블록(블록당 약 45행) · 주문 날짜순으로 쌓였다 · ITEM_ID NUMBER 1 ~ 92에 고르게 분포(상품당 약 26,900행, 하루 약 37행) · ORD_DT VARCHAR2(8) · ORD_HMS VARCHAR2(6) · ORD_ITEM_QTY NUMBER(1 ~ 9)
2013-11-15 하루 주문상품 약 3,430행 · 2013년 11월 약 102,900행 · 그중 자이언트더블버거(84) 1,121행
인덱스 ORD_ITEM_PK (ORD_NO, ITEM_ID) · ORD_ITEM_X01 (ORD_DT, ORD_HMS): BLEVEL 2 · 리프 약 9,300블록 · CLUSTERING_FACTOR 약 55,200 · 통계 최신
매일 03:00에 전날 주문상품 약 3,400행을 인덱스를 켠 채 일반 INSERT로 더한다
어림에 쓸 값: 블록 8KB에서 인덱스가 쓰는 공간은 약 7,200바이트(PCTFREE 10)다 · 항목 하나는 '컬럼 값 + 컬럼마다 길이 1바이트 + ROWID 7바이트 + 머리 약 4바이트'다 · ITEM_ID와 ORD_ITEM_QTY는 값이 2바이트, ORD_NO는 4바이트, ORD_DT는 8바이트, ORD_HMS는 6바이트다 · 브랜치 블록 하나는 아래 블록을 수백 개 가리킨다
참고(교재 04화 실측): 인덱스가 하나 늘면 행 하나를 넣을 때 블록 변경이 약 2회, redo가 약 236바이트 붙는다

원본 SQL

sql

-- 「메뉴 판매 내역」 (하루 수백 번)
SELECT ORD_DT, ORD_HMS, ORD_NO, ORD_ITEM_QTY
  FROM ORD_ITEM
 WHERE ITEM_ID = :v_item
   AND ORD_DT BETWEEN :v_from AND :v_to
 ORDER BY ORD_DT, ORD_HMS;

-- 조민우 승인서 A안 (예상 크기 · 높이 · 화면 예상 블록 · 적재 비용 : 빈칸)
CREATE INDEX ORD_ITEM_X03 ON ORD_ITEM (ITEM_ID, ORD_DT);

실행계획 학습용 예시 · 인덱스 규모는 교재 04화 실측 비례

xplan

[지금 · 자이언트더블버거 2013년 11월]
------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |      1 |        |   1121 |00:00:00.07 |    2687 |
|*  1 |  FILTER                      |              |      1 |        |   1121 |00:00:00.07 |    2687 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| ORD_ITEM     |      1 |    110 |   1121 |00:00:00.07 |    2687 |
|*  3 |    INDEX RANGE SCAN          | ORD_ITEM_X01 |      1 |  10107 |    102K|00:00:00.01 |     390 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(:V_FROM<=:V_TO)
   2 - filter("ITEM_ID"=:V_ITEM)
   3 - access("ORD_DT">=:V_FROM AND "ORD_DT"<=:V_TO)
(X01 순서가 ORDER BY와 같아 정렬은 없다)

[승인서 양식]  예상 크기(리프 블록 · MB) · 높이(BLEVEL) · 화면 예상 블록 · 적재 비용 · 전제하는 컬럼

문제

A안과 B안의 항목 크기, 리프 블록 수, 크기(MB), 높이를 어림하시오. 어림하는 방법이 맞는지 X01의 리프 9,300블록으로 검산하시오.
두 안으로 이 화면이 각각 몇 블록을 읽을지 어림하시오. A안의 CLUSTERING_FACTOR가 어느 정도일지, 테이블이 날짜순으로 쌓였는데도 왜 그런지 설명하시오.
두 안의 적재 비용을 비교하고 하나를 골라 승인서를 채우시오. DDL도 작성하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 승인서 (어림 · 블록 8KB · 인덱스가 쓰는 공간 약 7,200바이트 · 항목 = 컬럼 값 + 길이 1바이트씩 + ROWID 7 + 머리 4)
--                    A안 (ITEM_ID, ORD_DT)          B안 (ITEM_ID, ORD_DT, ORD_HMS, ORD_NO, ORD_ITEM_QTY)
--   항목 크기        3 + 9 + 7 + 4 = 23바이트        3 + 9 + 7 + 5 + 3 + 7 + 4 = 38바이트
--   리프 블록        247만 ÷ 313 ≈ 7,900             247만 ÷ 189 ≈ 13,100
--   크기 · 높이      약 62MB · BLEVEL 2              약 102MB · BLEVEL 2
--   화면 예상 블록   6 + 테이블 약 1,120 + 정렬       약 8 (테이블에 가지 않고 정렬도 없다)
--   적재 비용        하루 3,400행에 블록 변경 약 6,800 · redo 약 0.8MB (두 안이 같다)
--   전제하는 컬럼    —                                ITEM_ID · ORD_DT · ORD_HMS · ORD_NO · ORD_ITEM_QTY
CREATE INDEX ORD_ITEM_X03 ON ORD_ITEM (ITEM_ID, ORD_DT, ORD_HMS, ORD_NO, ORD_ITEM_QTY) INVISIBLE;
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;   -- 세션에서 화면을 재 어림과 맞는지 보고 → T2 → VISIBLE

SELECT ORD_DT, ORD_HMS, ORD_NO, ORD_ITEM_QTY                -- SQL 은 그대로
  FROM ORD_ITEM
 WHERE ITEM_ID = :v_item
   AND ORD_DT BETWEEN :v_from AND :v_to
 ORDER BY ORD_DT, ORD_HMS;

개선 후 실행계획 학습용 예시 · 인덱스 규모는 교재 04화 실측 비례

xplan

[B안 · 세션에서 잰 값 · 자이언트더블버거 2013년 11월]
---------------------------------------------------------------------------------------------
| Id  | Operation          | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |      1 |        |   1121 |00:00:00.01 |       8 |
|*  1 |  FILTER            |              |      1 |        |   1121 |00:00:00.01 |       8 |
|*  2 |   INDEX RANGE SCAN | ORD_ITEM_X03 |      1 |    110 |   1121 |00:00:00.01 |       8 |
---------------------------------------------------------------------------------------------
   1 - filter(:V_FROM<=:V_TO)
   2 - access("ITEM_ID"=:V_ITEM AND "ORD_DT">=:V_FROM AND "ORD_DT"<=:V_TO)

[만든 뒤 USER_INDEXES]  ORD_ITEM_X03 · BLEVEL 2 · LEAF_BLOCKS 13,052 · 약 102MB
[A안을 INVISIBLE로 잰 값]  INDEX RANGE SCAN 6 + TABLE ACCESS BY INDEX ROWID BATCHED 1,118 + SORT ORDER BY = 1,124 · CLUSTERING_FACTOR 2,438,906

모범 답안 서술

어림 방법부터 검산한다. 인덱스 항목 하나는 '컬럼 값 + 컬럼마다 길이 1바이트 + ROWID 7바이트 + 머리 약 4바이트'이고, 블록 하나에 약 7,200바이트를 쓴다. X01 (ORD_DT, ORD_HMS)는 9 + 7 + 7 + 4 = 27바이트라 리프 한 블록에 약 266항목이 들어가고, 2,472,717 ÷ 266 ≈ 9,300블록으로 실측과 맞는다. 같은 방법으로 A안 (ITEM_ID, ORD_DT)는 3 + 9 + 7 + 4 = 23바이트, 리프 한 블록에 313항목, 리프 약 7,900블록(약 62MB)이다. B안은 ORD_HMS, ORD_NO, ORD_ITEM_QTY가 더해져 38바이트, 189항목, 리프 약 13,100블록(약 102MB)이다. 높이는 둘 다 BLEVEL 2다. 브랜치 하나가 아래 블록을 수백 개 가리키므로 리프 1만여 개는 루트 아래 브랜치 한 단계로 담긴다. 화면 블록은 크게 갈린다. 지금은 X01로 한 달 치 약 102,900항목(리프 약 390블록)과 테이블 약 2,300블록을 읽고 1,121행만 남긴다(2,687). A안은 자이언트더블버거의 한 달 1,121항목을 리프 4블록에서 바로 찾지만, 1,121행을 테이블에서 하나씩 읽는다. 테이블은 날짜순으로 쌓였어도 같은 날 같은 메뉴의 37행은 그날 주문상품 3,430행 사이에 약 92행 간격으로 끼어 있어 거의 모두 다른 블록에 있다. 그래서 A안의 CLUSTERING_FACTOR는 행 수에 가까운 약 244만이고, 화면은 6 + 약 1,120 = 약 1,124블록에 시각순 정렬까지 붙는다. B안은 시각과 주문번호, 수량까지 인덱스에 있고 그 순서로 놓여 있어 테이블에 가지 않고 정렬도 없다. 리프 6블록과 수직 탐색 2블록, 약 8블록이다. 적재 비용은 두 안이 같다. 인덱스가 하나 늘 때 붙는 비용은 폭이 아니라 개수에 달려 있어서, 매일 03:00의 3,400행에 블록 변경 약 6,800회와 redo 약 0.8MB가 붙는다(행 하나에 인덱스 하나마다 블록 변경 약 2회, redo 약 236바이트). 그래서 B안을 고른다. A안보다 40MB 크고 만드는 데 몇 초 더 걸리지만, 그 값으로 화면이 2,687 → 8블록이 된다(A안이라면 1,124). 승인서의 '전제하는 컬럼'에는 ITEM_ID, ORD_DT, ORD_HMS, ORD_NO, ORD_ITEM_QTY를 적는다.

해설

어림이 틀리는 곳: 항목 크기 어림은 '갓 만든' 인덱스 기준이다. 끼어드는 INSERT를 받아 블록이 쪼개지면 리프는 어림보다 커진다. ITEM_ID마다 92곳의 끝에 쌓이는 이 인덱스는 그 영향이 작다. 만든 뒤 USER_INDEXES.LEAF_BLOCKS로 어림과 맞춰 본다.
A안이 나을 때: 화면이 메뉴 하나의 하루(37행)만 본다면 A안도 테이블 37블록이면 끝난다. 범위가 한 달(1,121행)이라 테이블 액세스가 화면 일량의 대부분이 된다. 승인서의 '화면 예상 블록'은 화면이 고르는 범위로 계산한다.
CF는 만들기 전에 어림할 수 있다: 인덱스 순서대로 읽을 때 다음 행이 같은 블록에 있을 가능성을 생각한다. 테이블이 쌓인 순서와 인덱스 선두 컬럼이 같으면 블록 수에 가깝고(X01 약 55,200), 선두가 다른 컬럼이면 행 수에 가까워진다(교재 04화 §1).
커버링이 테이블만큼 커질 때: 이 테이블은 행이 넓어(블록당 약 45행) B안이 테이블 55,265블록의 4분의 1이지만, 행이 좁은 테이블에서 컬럼을 다 담으면 인덱스가 테이블보다 커지기도 한다(ACC-069). 그때는 화면 빈도와 크기를 함께 적어 판단한다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 인덱스 규모는 교재 04화 실측 비례 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 인덱스 규모는 교재 04화 실측 비례 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황