「메뉴별 월 판매 추이」 24개월 그래프가 열 때마다 주문상품 5만 5천 블록을 다시 묶는다

중급SELECT버거371 / 700

상황

버거 본사 메뉴팀의 「메뉴별 월 판매 추이」는 메뉴 92종의 최근 24개월 월별 판매 수량을 그래프로 그린다. 메뉴팀 15명이 하루 약 300번 여는데, 열 때마다 2.4초가 걸린다. 이번 달 칸은 지금까지의 숫자가 보여야 하고, 지난달까지의 칸은 매월 2일 새벽의 월 마감 뒤에는 바뀌지 않는다(정정은 이번 달 주문으로 들어온다). 조민우가 인덱스를 만들자고 하자 DBA는 "24개월을 다 묶는 SQL이면 인덱스를 만들어도 FULL이 맞다"라고 했다. 오늘은 2013년 12월 31일이다.

테이블과 인덱스

ORD_ITEM 주문상품: 2,472,717행 · 55,265블록 · (ORD_NO, ITEM_ID) 순으로 쌓여 있다(주문번호는 시각 순이라 날짜순과 거의 같다) · ITEM_ID 1 ~ 92 · ORD_DT VARCHAR2(8) 2012-01-01 ~ 2013-12-31 · ORD_ITEM_QTY · 한 달 약 103,000행
인덱스 ORD_ITEM_PK (ORD_NO, ITEM_ID) · ORD_ITEM_X01 (ORD_DT, ORD_HMS): BLEVEL 2 · 리프 약 9,320 · CF 약 55,200 · 통계 최신
주문상품은 POS 결제마다 1 ~ 4줄씩 들어온다

원본 SQL

sql

SELECT SUBSTR(ORD_DT, 1, 6) AS YM, ITEM_ID, SUM(ORD_ITEM_QTY) AS QTY
  FROM ORD_ITEM
 WHERE ORD_DT >= :from_ym || '01'          -- :from_ym = '201201' (24개월 전)
 GROUP BY SUBSTR(ORD_DT, 1, 6), ITEM_ID
 ORDER BY YM, ITEM_ID

실행계획 학습용 예시

xplan

-----------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |      1 |        |   2208 |00:00:02.41 |   55265 |          |
|   1 |  SORT GROUP BY     |          |      1 |   2208 |   2208 |00:00:02.41 |   55265 |  178K (0)|
|*  2 |   TABLE ACCESS FULL| ORD_ITEM |      1 |   2472K|   2472K|00:00:00.73 |   55265 |          |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("ORD_DT">=:FROM_YM||'01')

문제

24개월 집계에 ORD_ITEM_X01을 쓰지 않고 FULL로 읽는 것이 옳은 까닭을 숫자로 설명하시오.
화면 요구(이번 달은 지금까지, 지난달까지는 마감 숫자)를 지키면서 화면을 열 때 읽는 양을 줄이도록 SQL과 DDL을 쓰시오.
고친 구조의 대가와 정합성을 지키는 조건을 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 마감 집계 표 : 마감된 달은 한 번만 묶어 둔다 (23개월 × 92종 = 2,116 줄)
CREATE TABLE ORD_ITEM_MON (
  YM       VARCHAR2(6) NOT NULL,
  ITEM_ID  NUMBER      NOT NULL,
  QTY      NUMBER      NOT NULL,
  CONSTRAINT ORD_ITEM_MON_PK PRIMARY KEY (YM, ITEM_ID)
);

-- 처음 한 번 : 지난 23개월을 채운다
INSERT INTO ORD_ITEM_MON (YM, ITEM_ID, QTY)
SELECT SUBSTR(ORD_DT, 1, 6), ITEM_ID, SUM(ORD_ITEM_QTY)
  FROM ORD_ITEM
 WHERE ORD_DT < '20131201'
 GROUP BY SUBSTR(ORD_DT, 1, 6), ITEM_ID;

-- 월 마감 배치 (매월 2일) : 지난달 범위만 X01 로 읽어 한 달 치를 넣는다
INSERT INTO ORD_ITEM_MON (YM, ITEM_ID, QTY)
SELECT :last_ym, ITEM_ID, SUM(ORD_ITEM_QTY)
  FROM ORD_ITEM
 WHERE ORD_DT >= :last_ym || '01'
   AND ORD_DT <  :this_ym || '01'
 GROUP BY ITEM_ID;

-- ② 화면 : 마감된 달은 집계 표에서, 이번 달만 원장에서
SELECT YM, ITEM_ID, QTY
  FROM ORD_ITEM_MON
 WHERE YM >= :from_ym
   AND YM <  :this_ym
UNION ALL
SELECT :this_ym, ITEM_ID, SUM(ORD_ITEM_QTY)
  FROM ORD_ITEM
 WHERE ORD_DT >= :this_ym || '01'
 GROUP BY ITEM_ID
ORDER BY 1, 2;

개선 후 실행계획 학습용 예시

xplan

[화면 · 고친 뒤 · :this_ym = '201312']
------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |              |      1 |        |   2208 |00:00:00.11 |    2714 |          |
|   1 |  SORT ORDER BY                         |              |      1 |   2208 |   2208 |00:00:00.11 |    2714 |  178K (0)|
|   2 |   UNION-ALL                            |              |      1 |        |   2208 |00:00:00.11 |    2714 |          |
|*  3 |    TABLE ACCESS FULL                   | ORD_ITEM_MON |      1 |   2116 |   2116 |00:00:00.01 |      11 |          |
|   4 |    HASH GROUP BY                       |              |      1 |     92 |     92 |00:00:00.10 |    2703 | 1043K (0)|
|   5 |     TABLE ACCESS BY INDEX ROWID BATCHED| ORD_ITEM     |      1 |    103K|    103K|00:00:00.07 |    2703 |          |
|*  6 |      INDEX RANGE SCAN                  | ORD_ITEM_X01 |      1 |    103K|    103K|00:00:00.01 |     392 |          |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(("YM">=:FROM_YM AND "YM"<:THIS_YM))
   6 - access("ORD_DT">=:THIS_YM||'01')

모범 답안 서술

24개월 집계는 주문상품 247만 행 거의 전부(2012-01 ~ 2013-12)를 묶어야 한다. ORD_ITEM_X01로 읽으면 리프 약 9,320블록에 더해 테이블을 행마다 찾아가는데, CF가 약 55,200이라 테이블 블록 대부분을 한 블록씩 다시 읽어 FULL(55,265블록, 멀티블록 읽기)보다 비싸다. 범위가 테이블 전체에 가까우면 인덱스가 이길 수 없다. DBA의 말이 맞다. 줄일 거리는 다른 데 있다. 23개월 칸은 마감 뒤 바뀌지 않는데도 화면을 열 때마다 처음부터 다시 묶는다. 바뀌지 않는 답을 하루 300번 다시 계산하는 셈이다. 그래서 마감된 달은 월 마감 배치가 한 번 묶어 집계 표 ORD_ITEM_MON(23개월 × 92종 = 2,116줄)에 넣어 두고, 화면은 그 표와 이번 달만 원장에서 묶은 92줄을 UNION ALL로 붙인다. 이번 달 한 달(약 103,000행)은 ORD_ITEM_X01 범위로 읽는다. 날짜순으로 쌓인 테이블이라 한 달 치가 모여 있어 리프 392블록과 테이블 약 2,300블록이면 된다. 화면의 Buffers는 55,265 → 2,714, 2.4초 → 0.11초, 하루 약 1,660만 → 81만 블록이다. 대가와 조건이 있다. 월 마감 배치에 한 단계가 늘고(한 달 범위만 읽어 약 2,700블록), 집계 표가 원장과 같다는 전제를 지켜야 한다. 마감 뒤 지난달 주문상품이 바뀌지 않는다는 규칙이 그 전제이므로, 규칙이 깨지면(지난달 정정) 그 달을 다시 마감해 넣어야 한다. 마감 직후에는 집계 표의 그달 합계를 원장 합계와 대조한다. 이번 달까지 인덱스만으로 끝내려면 (ORD_DT, ITEM_ID, ORD_ITEM_QTY) 같은 커버링 인덱스가 필요한데, POS 결제마다 고칠 인덱스가 하나 늘어나므로 2,714블록이면 충분하다고 보고 만들지 않는다.

해설

구체화 뷰로 만들어도 된다: 마감 집계 표 대신 ORD_ITEM 집계를 MV로 두고 월 마감 때 새로 고쳐도 같다. 다만 MV를 통째로 새로 고치면 24개월을 다시 묶으므로, 마감된 달만 더하는 지금 방식이 새로 고침 비용은 더 작다. MV의 재작성과 새로 고침의 대가는 SORT-067 ~ 070에서 다룬다.
'지금까지'를 버리면 더 싸다: 이번 달 칸도 어젯밤 기준이면 충분하다면 밤마다 이번 달 칸을 다시 넣고 화면은 집계 표만 읽는다(약 11블록). 하지만 메뉴팀이 신메뉴 출시일에 오늘 판매를 보려고 이 화면을 연다면 요구를 바꿀 수 없다. 그 판단은 화면 주인이 한다(교재 08화 §9).
GROUP BY SUBSTR(ORD_DT, 1, 6)처럼 묶는 키를 가공하는 것은 집계에 문제가 없다. 가공이 문제가 되는 것은 WHERE 절에서 범위를 잡을 때다. 이번 달 조건은 ORD_DT >= :this_ym || '01'처럼 컬럼을 그대로 두어야 X01의 범위 스캔이 된다.
집계 표를 처음 채운 뒤에는 지금 SQL의 결과와 대조한다. 23개월 칸의 합계가 같고 MINUS 양방향이 0 / 0이어야 화면을 바꾼다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황