리포트를 중간 집계 테이블에서 뽑게 바꾸자 매장 월 주문 수가 원장의 2.5배로 찍힌다

중급INSERT버거486 / 700

상황

버거원 본사 BI팀은 매월 리포트 두 개(매장 월 리포트, 상품 월 리포트)를 원장(ORD와 ORD_ITEM)에서 직접 집계해 왔다. 리포트 하나에 약 3,500블록씩 읽는다. 이번 달부터 매일 밤 다시 계산해 두는 중간 집계 테이블 SHOP_ITEM_MON에서 두 리포트를 뽑게 바꿨고, 리포트 하나가 150블록 남짓으로 줄었다. 12월 리포트를 받은 영업팀이 물었다. "매장 주문 수가 왜 작년보다 두 배 넘게 늘었죠? 주문당 수량은 1.4개로 떨어졌고요." 상품 월 리포트의 상품별 주문 수는 원장과 맞는다.

테이블과 인덱스

ORD 주문: 1,000,000행 · 22,422블록 · 날짜순 적재 · ORD_X01 (ORD_DT, ORD_HMS) · 2013년 12월 42,408건 · SHOP_NO 200개
ORD_ITEM 주문상품: 2,472,717행 · 55,265블록 · ORD_ITEM_PK (ORD_NO, ITEM_ID)(주문 하나에 같은 상품은 한 줄) · 12월 106,342행 · 12월 판매 수량 합계 148,879개
SHOP_ITEM_MON(중간 집계, 이 문제용): PK SHOP_ITEM_MON_PK (ORD_YM, SHOP_NO, ITEM_ID) · SALE_QTY(수량 합) · LINE_CNT(주문상품 줄 수) · ORD_CNT(그 매장에서 그 상품이 들어간 주문 수, COUNT(DISTINCT ORD_NO)) · 매일 밤 그 달 분을 다시 계산한다 · 12월 17,934행
RPT_SHOP_MON, RPT_ITEM_MON(리포트, 이 문제용): 매달 그 달 행을 지우고 다시 넣는다. 통계는 최신이다.

원본 SQL

sql

-- 이번 달부터: 리포트 둘을 중간 집계 SHOP_ITEM_MON 에서 만든다
INSERT INTO RPT_SHOP_MON (ORD_YM, SHOP_NO, ORD_CNT, SALE_QTY, QTY_PER_ORD)
SELECT ORD_YM, SHOP_NO, SUM(ORD_CNT), SUM(SALE_QTY), ROUND(SUM(SALE_QTY) / SUM(ORD_CNT), 2)
  FROM SHOP_ITEM_MON
 WHERE ORD_YM = :ym
 GROUP BY ORD_YM, SHOP_NO;

INSERT INTO RPT_ITEM_MON (ORD_YM, ITEM_ID, ORD_CNT, SALE_QTY)
SELECT ORD_YM, ITEM_ID, SUM(ORD_CNT), SUM(SALE_QTY)
  FROM SHOP_ITEM_MON
 WHERE ORD_YM = :ym
 GROUP BY ORD_YM, ITEM_ID;

실행계획 학습용 예시

xplan

[매장 월 리포트 — 12월]
----------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                     |                  |      1 |        |      0 |00:00:00.03 |     151 |
|   1 |  LOAD TABLE CONVENTIONAL             | RPT_SHOP_MON     |      1 |        |      0 |00:00:00.03 |     151 |
|   2 |   HASH GROUP BY                      |                  |      1 |    200 |    200 |00:00:00.02 |     138 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| SHOP_ITEM_MON   |      1 |  17934 |  17934 |00:00:00.01 |     138 |
|*  4 |     INDEX RANGE SCAN                 | SHOP_ITEM_MON_PK |      1 |  17934 |  17934 |00:00:00.01 |      58 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("ORD_YM"=:YM)

[12월 대조 — 원장 직접 집계와 두 리포트]
                            원장(직접 집계)   RPT_SHOP_MON   RPT_ITEM_MON
  주문 수 합계                     42,408        106,342        106,342 (상품별 주문 수를 모두 더한 값)
  상품 I0007 주문 수                9,815              —          9,815
  매장 SH0001 주문 수                 207            519              —
  판매 수량 합계                  148,879        148,879        148,879
  주문당 수량                        3.51           1.40              —

문제

매장 월 리포트의 주문 수가 원장의 약 2.5배로 나온 이유를 설명하시오. 같은 SUM(ORD_CNT)인데 상품 월 리포트의 상품별 주문 수는 왜 맞는지도 쓰시오.
중간 집계를 읽는 이점은 살리면서 매장 월 리포트를 바로잡으시오. 중간 집계 테이블에 둘 수 있는 값과 그대로 더하면 안 되는 값을 가르는 기준도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 한 번만: 매장 단위 중간 집계 (주문 수처럼 상품으로 쪼개면 더할 수 없게 되는 값을 둔다)
CREATE TABLE SHOP_MON (
  ORD_YM  VARCHAR2(6),
  SHOP_NO VARCHAR2(6),
  ORD_CNT NUMBER,
  CONSTRAINT SHOP_MON_PK PRIMARY KEY (ORD_YM, SHOP_NO)
);

-- ① 매일 밤 SHOP_ITEM_MON 을 다시 계산하는 단계에 덧붙인다: 주문 수는 주문 머리에서 매장 단위로
DELETE FROM SHOP_MON WHERE ORD_YM = :ym;
INSERT INTO SHOP_MON (ORD_YM, SHOP_NO, ORD_CNT)
SELECT :ym, SHOP_NO, COUNT(*)
  FROM ORD
 WHERE ORD_DT BETWEEN :ym || '01' AND :ym || '31'
 GROUP BY SHOP_NO;

-- ② 매장 월 리포트: 더할 수 있는 수량은 SHOP_ITEM_MON 에서, 주문 수는 SHOP_MON 에서 · 비율은 마지막에
INSERT INTO RPT_SHOP_MON (ORD_YM, SHOP_NO, ORD_CNT, SALE_QTY, QTY_PER_ORD)
SELECT s.ORD_YM, s.SHOP_NO, s.ORD_CNT, q.SALE_QTY, ROUND(q.SALE_QTY / s.ORD_CNT, 2)
  FROM SHOP_MON s,
       (SELECT SHOP_NO, SUM(SALE_QTY) AS SALE_QTY
          FROM SHOP_ITEM_MON
         WHERE ORD_YM = :ym
         GROUP BY SHOP_NO) q
 WHERE s.ORD_YM = :ym
   AND q.SHOP_NO = s.SHOP_NO;

-- ③ 상품 월 리포트는 그대로 둔다
-- ④ 대조: SELECT SUM(ORD_CNT) FROM SHOP_MON WHERE ORD_YM = :ym  = 원장 주문 수 (42,408)

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

xplan

[① SHOP_MON 만들기 — 매일 밤 한 번]
---------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                     |         |      1 |        |      0 |00:00:00.05 |    1081 |
|   1 |  LOAD TABLE CONVENTIONAL             | SHOP_MON|      1 |        |      0 |00:00:00.05 |    1081 |
|   2 |   HASH GROUP BY                      |         |      1 |    200 |    200 |00:00:00.04 |    1072 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| ORD    |      1 |  42408 |  42408 |00:00:00.03 |    1072 |
|*  4 |     INDEX RANGE SCAN                 | ORD_X01 |      1 |  42408 |  42408 |00:00:00.01 |     119 |
---------------------------------------------------------------------------------------------------
   4 - access("ORD_DT">=:YM||'01' AND "ORD_DT"<=:YM||'31')

[② 매장 월 리포트]
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                        |                  |      1 |        |      0 |00:00:00.03 |     157 |
|   1 |  LOAD TABLE CONVENTIONAL                | RPT_SHOP_MON     |      1 |        |      0 |00:00:00.03 |     157 |
|*  2 |   HASH JOIN                             |                  |      1 |    200 |    200 |00:00:00.02 |     141 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED  | SHOP_MON         |      1 |    200 |    200 |00:00:00.01 |       3 |
|*  4 |     INDEX RANGE SCAN                    | SHOP_MON_PK      |      1 |    200 |    200 |00:00:00.01 |       2 |
|   5 |    VIEW                                 |                  |      1 |    200 |    200 |00:00:00.02 |     138 |
|   6 |     HASH GROUP BY                       |                  |      1 |    200 |    200 |00:00:00.02 |     138 |
|   7 |      TABLE ACCESS BY INDEX ROWID BATCHED| SHOP_ITEM_MON    |      1 |  17934 |  17934 |00:00:00.01 |     138 |
|*  8 |       INDEX RANGE SCAN                  | SHOP_ITEM_MON_PK |      1 |  17934 |  17934 |00:00:00.01 |      58 |
-------------------------------------------------------------------------------------------------------------
   2 - access("Q"."SHOP_NO"="S"."SHOP_NO")
   4 - access("S"."ORD_YM"=:YM)
   8 - access("ORD_YM"=:YM)

  대조: 주문 수 합계 42,408 = 원장 · SH0001 207 · 주문당 수량 3.51

모범 답안 서술

SHOP_ITEM_MON의 ORD_CNT는 (매장, 상품)마다 그 상품이 들어간 주문을 센 값이다. 주문 하나에 상품이 세 가지 들어 있으면 그 주문은 세 행에서 한 번씩 세어진다. 그래서 매장 단위로 SUM(ORD_CNT)를 하면 한 주문을 상품 가짓수만큼 겹쳐 세게 된다. ORD_ITEM의 PK가 (주문, 상품)이므로 12월 합계는 주문상품 줄 수와 같은 106,342가 되고, 원장의 주문 42,408건의 약 2.5배다. 주문당 수량도 148,879 ÷ 106,342 = 1.40으로 잘못 나온다. 실행계획은 138블록으로 훌륭하지만 숫자가 틀렸다. 같은 SUM(ORD_CNT)라도 상품 월 리포트는 맞다. 상품 월 리포트는 같은 상품의 행을 매장끼리 더하는데, 주문 하나는 매장 하나에만 속하므로 매장끼리는 겹치는 주문이 없다. COUNT(DISTINCT)로 센 값은 그 값(주문)이 겹치지 않게 나뉘는 축(매장, 날짜)으로는 더할 수 있고, 겹치는 축(상품)으로는 더할 수 없다. 고친 판은 매장 단위 중간 집계 SHOP_MON을 하나 더 둔다. 매일 밤 SHOP_ITEM_MON을 다시 계산하는 단계에서 주문 머리 ORD를 매장별로 세어 200행을 넣는다(약 1,080블록). 매장 월 리포트는 수량을 SHOP_ITEM_MON에서 더하고 주문 수는 SHOP_MON에서 가져와, 주문당 수량은 마지막에 합계끼리 나눈다. 주문 수는 106,342 → 42,408, 주문당 수량은 1.40 → 3.51로 원장과 맞고, 리포트 하나는 여전히 157블록이다(원장 직접 집계 약 3,500블록). 중간 집계 테이블에는 어떤 축으로 다시 묶어도 그대로 더할 수 있는 값(수량 합, 금액 합, 줄 수)을 둔다. 평균과 비율은 저장하지 않고 합계와 건수를 둔 뒤 리포트에서 나눈다. COUNT(DISTINCT)처럼 더할 수 없는 값은 그 값이 겹치지 않는 단위(주문이면 매장, 날짜)의 중간 집계를 따로 두고, 원장 합계와 대조한 뒤에 쓴다.

해설

대조의 기준은 원장이다: 리포트를 바꾸고 원래 판과 숫자를 맞춰 봤다면 바로 드러났을 차이다. 중간 집계로 바꿀 때는 원장 직접 집계와 건수, 합계를 리포트마다 대조한다(교재 08화 체크리스트 A).
다른 정답: SHOP_ITEM_MON에 행을 더하지 않고 매장 월 리포트만 원장에서 ORD를 세어 붙여도 맞는다(약 1,080블록). 매장 단위 숫자를 쓰는 리포트가 여럿이면 SHOP_MON처럼 한 번 세어 두는 쪽이 낫다.
가장 잘게 묶은 표에서 다 만들려는 유혹: (매장, 상품, 일)처럼 잘게 묶을수록 행 수가 원장에 가까워져 이점이 줄고, 더할 수 없는 값은 여전히 따로 둬야 한다. 같은 달을 여러 묶음으로 한 번에 집계하는 방법(GROUPING SETS와 INSERT ALL)은 SORT 단원에서 다룬다.
평균도 같은 함정이 있다. 매장별 평균 객단가를 저장해 두고 지역으로 다시 평균하면 매장마다 주문 수가 달라 틀린다. 합계와 건수를 두고 마지막에 나눈다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황