새 인덱스로 상품 일 판매는 빨라졌는데 월간 추이는 345블록을 읽는다

중급DDL버거86 / 700

상황

버거원 상품기획팀은 두 화면을 쓴다. 「상품 일 판매」는 날짜와 상품 하나를 골라 판매 수량을 보고, 「상품 월간 추이」는 상품 하나의 한 달 일별 판매 수량을 그린다. 둘 다 하루 수백 번 연다. 조민우가 두 화면을 위한 인덱스 요청서를 올렸다. "(ORD_DT, ITEM_ID, ORD_ITEM_QTY). ORD_DT는 730종, ITEM_ID는 92종이므로 선택도가 좋은 ORD_DT를 앞에 둔다." INVISIBLE로 만들어 재 보니 일 판매는 4블록인데 월간 추이는 345블록이다.

테이블과 인덱스

ORD_ITEM 주문상품: 2,472,717행 · 55,265블록 · 주문 날짜순으로 적재 · ITEM_ID 1 ~ 92에 고르게 분포(상품당 약 26,900행, 하루 약 37행) · ORD_DT VARCHAR2(8) 2012 ~ 2013 · 2013년 11월 약 102,900행
자이언트더블버거 ITEM_ID = 84: 2013년 11월 1,121행
인덱스 ORD_ITEM_PK (ORD_NO, ITEM_ID) · ORD_ITEM_X01 (ORD_DT, ORD_HMS)(CF 좋음)
조민우 안 ORD_ITEM_X02 (ORD_DT, ITEM_ID, ORD_ITEM_QTY): INVISIBLE · BLEVEL 2 · 리프 약 8,500블록(리프 한 블록에 약 290항목)

원본 SQL

sql

-- S1 「상품 일 판매」
SELECT SUM(ORD_ITEM_QTY) AS qty, COUNT(*) AS line_cnt
  FROM ORD_ITEM
 WHERE ORD_DT  = :v_dt
   AND ITEM_ID = :v_item;

-- S2 「상품 월간 추이」
SELECT ORD_DT, SUM(ORD_ITEM_QTY) AS qty
  FROM ORD_ITEM
 WHERE ITEM_ID = :v_item
   AND ORD_DT BETWEEN :v_from AND :v_to
 GROUP BY ORD_DT
 ORDER BY ORD_DT;

-- 요청서
CREATE INDEX ORD_ITEM_X02 ON ORD_ITEM (ORD_DT, ITEM_ID, ORD_ITEM_QTY) INVISIBLE;

실행계획 학습용 예시 · 규모는 schemas 그대로

xplan

[지금 — 요청서 인덱스 없이] S1 ORD_ITEM_X01 + 테이블에서 상품을 거른다 91 · S2 ORD_ITEM_X01 + 테이블 2,648

[요청서 X02 · 세션에서만 — S1 (2013-11-02, 상품 84)]
-----------------------------------------------------------------------------------------------
| Id  | Operation          | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |      1 |        |      1 |00:00:00.01 |       4 |
|   1 |  SORT AGGREGATE    |              |      1 |      1 |      1 |00:00:00.01 |       4 |
|*  2 |   INDEX RANGE SCAN | ORD_ITEM_X02 |      1 |     37 |     38 |00:00:00.01 |       4 |
-----------------------------------------------------------------------------------------------
   2 - access("ORD_DT"=:V_DT AND "ITEM_ID"=:V_ITEM)

[요청서 X02 · 세션에서만 — S2 (2013년 11월, 상품 84)]
-----------------------------------------------------------------------------------------------
| Id  | Operation          | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |      1 |        |     30 |00:00:00.02 |     345 |
|   1 |  SORT GROUP BY NOSORT|            |      1 |     41 |     30 |00:00:00.02 |     345 |
|*  2 |   FILTER           |              |      1 |        |   1121 |00:00:00.02 |     345 |
|*  3 |    INDEX RANGE SCAN| ORD_ITEM_X02 |      1 |     41 |   1121 |00:00:00.02 |     345 |
-----------------------------------------------------------------------------------------------
   2 - filter(:V_FROM<=:V_TO)
   3 - access("ORD_DT">=:V_FROM AND "ITEM_ID"=:V_ITEM AND "ORD_DT"<=:V_TO)
       filter("ITEM_ID"=:V_ITEM)

문제

S1은 어느 순서로 만들어도 같은 양을 읽는데 S2는 그렇지 않은 이유를 access와 filter로 설명하시오.
두 화면에 맞는 인덱스를 설계해 DDL을 작성하시오. 요청서의 근거('선택도가 좋은 컬럼을 앞에')가 왜 틀렸는지 설명하고, 그 인덱스의 대가도 적으시오.

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

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

모범답안

모범 답안 SQL

sql

-- = 로 들어오는 ITEM_ID 를 앞에, 범위인 ORD_DT 를 뒤에, 합계 대상 수량까지 담는다
CREATE INDEX ORD_ITEM_X02 ON ORD_ITEM (ITEM_ID, ORD_DT, ORD_ITEM_QTY) INVISIBLE;
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;   -- S1 · S2 를 세션에서 재고 → T2 → VISIBLE
-- S1 · S2 는 그대로 · 날짜로만 찾는 SQL 은 ORD_ITEM_X01 을 그대로 쓴다

개선 후 실행계획 학습용 예시 · 규모는 schemas 그대로

xplan

[S2 — (ITEM_ID, ORD_DT, ORD_ITEM_QTY) · 2013년 11월 · 상품 84]
-----------------------------------------------------------------------------------------------
| Id  | Operation          | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |      1 |        |     30 |00:00:00.01 |       6 |
|   1 |  SORT GROUP BY NOSORT|            |      1 |     41 |     30 |00:00:00.01 |       6 |
|*  2 |   FILTER           |              |      1 |        |   1121 |00:00:00.01 |       6 |
|*  3 |    INDEX RANGE SCAN| ORD_ITEM_X02 |      1 |     41 |   1121 |00:00:00.01 |       6 |
-----------------------------------------------------------------------------------------------
   2 - filter(:V_FROM<=:V_TO)
   3 - access("ITEM_ID"=:V_ITEM AND "ORD_DT">=:V_FROM AND "ORD_DT"<=:V_TO)

[S1] 4 블록 그대로 · access("ITEM_ID"=:V_ITEM AND "ORD_DT"=:V_DT)

모범 답안 서술

S1은 두 조건이 모두 =이다. (ORD_DT, ITEM_ID)든 (ITEM_ID, ORD_DT)든 '그날의 그 상품' 항목은 인덱스 안에서 연속된 한 구간이고, 그 구간의 크기(약 37항목)는 순서와 상관없다. 그래서 어느 쪽이든 4블록이다. = 조건끼리는 선택도가 일량을 바꾸지 못한다. 순서는 범위 조건이 들어오는 S2가 정한다. S2는 상품이 =, 날짜가 범위다. 요청서대로 ORD_DT를 앞에 두면, S2의 3번 줄처럼 범위 조건 ORD_DT 뒤의 ITEM_ID는 시작점과 끝점만 좁히고 filter로 거른다. 그래서 한 달 치 약 10만 항목(리프 약 343블록)을 읽어 상품 84의 1,121건을 남긴다(Buffers 345). =로 들어오는 ITEM_ID를 앞에, 범위인 ORD_DT를 뒤에 둔 (ITEM_ID, ORD_DT, ORD_ITEM_QTY)는 S2에서 '상품 84의 11월'이 한 구간이 되어 리프 4블록만 읽는다(345 → 6). S1도 4블록 그대로다. 수량까지 담았으므로 두 SQL 모두 테이블에 가지 않는다(인덱스가 없는 지금은 S1 91, S2 2,648 → 6). 요청서의 근거인 '종류가 많은 컬럼을 앞에'는 기준이 아니다. 첫 기준은 조건의 모양(=인지 범위인지)이고, 종류 수는 = 조건 컬럼끼리 다른 사정이 없을 때나 따진다. 대가도 적는다. ORD_ITEM은 주문 한 건에 1 ~ 4행이 들어가므로, 리프 약 8,500블록짜리 인덱스가 하나 늘면 주문 INSERT마다 인덱스 항목을 여러 개 더 고친다(적재 비용). 날짜로만 찾는 SQL은 ORD_ITEM_X01을 그대로 쓴다.

해설

SQL 하나만 보면 컬럼 순서를 정할 수 없을 때가 많다. S1만 있었다면 두 순서 모두 정답이다. 순서를 가르는 것은 범위 조건이 들어오는 SQL이다.
= 조건 컬럼끼리의 순서는 다른 기준으로 정한다. 그 컬럼만 따로 나오는 SQL이 있는지, IN 목록이나 SKIP SCAN으로 쓸 일이 있는지, 정렬 순서와 맞는지를 본다. 선택도는 그다음이다(교재 04화 §6의 네 기준).
WHERE 절에 조건을 쓰는 순서는 실행계획과 상관없다. S2의 조건을 ORD_DT BETWEEN …부터 써도 계획은 그대로다. 바꿔야 하는 것은 인덱스 컬럼 순서다.
날짜 하루치만 읽는 SQL이 이 인덱스를 쓰려면 ITEM_ID가 선두라 92개 상품마다 건너뛰는 SKIP SCAN이 된다. ORD_ITEM_X01이 이미 있으므로 필요 없다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 규모는 schemas 그대로 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 규모는 schemas 그대로 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황