지난달 매장 일 집계를 다시 맞추는 배치가 48만 블록을 읽고, 주문을 지운 날의 숫자는 그대로 남긴다

실전MERGE버거454 / 700

상황

버거원 본사의 매장 화면은 매장 일 집계 테이블(SHOP_DAY)만 읽는다. 매일 밤 전날 하루치를 넣고, 매월 3일 02:00에는 늦게 올라온 POS 주문을 반영하려고 지난달 한 달 치를 다시 맞춘다. 12월분을 다시 맞추는 데 8초 남짓이 걸렸다. 그런데 SH0077 매장의 12월 24일은 테스트 주문 61건을 지웠는데도 화면에 주문 61건이 그대로 나온다. 개발팀은 MERGE로 바꾸자는 쪽과 그 달을 지우고 다시 넣자는 쪽으로 갈렸다. 박팀장은 세 방법을 비교한 근거를 남기라고 했다.

테이블과 인덱스

ORD 주문: 1,000,000행 · 22,422블록 · ORD_PK (ORD_NO) · ORD_X01 (ORD_DT, ORD_HMS) · 날짜순 적재 · 2013년 12월 42,408행(하루 약 1,370건, 매장 하루 약 7건)
ORD_ITEM 주문상품: 2,472,717행 · 55,265블록 · ORD_ITEM_PK (ORD_NO, ITEM_ID) 높이 3 · ORD_ITEM_X01 (ORD_DT, ORD_HMS) · ORD_DT는 ORD에서 복사해 둔 칸 · 12월분 104,712행
SHOP_DAY(매장 일 집계, 이 문제를 위해 둔 테이블): 146,000행 · 524블록 · PK (SHOP_NO, ORD_DT) · ORD_CNT, ITEM_QTY(둘 다 NOT NULL) · 12월분 6,200행(200매장 × 31일)
다시 집계하면 12월에 숫자가 바뀌는 행 214행, 새로 생길 행 3행(그날 첫 주문이 늦게 올라온 매장), 주문이 0건이 되는 행 1행(SH0077의 12월 24일)
매장 화면은 하루 종일 SHOP_DAY를 읽는다. 다른 테이블이 SHOP_DAY를 참조하지는 않는다.

원본 SQL

sql

-- 매월 3일 02:00 · 지난달 매장 일 집계 다시 맞추기 (늦게 올라온 POS 반영)
-- ① 이미 있는 날은 고친다
UPDATE SHOP_DAY t
   SET (t.ORD_CNT, t.ITEM_QTY) =
       (SELECT COUNT(DISTINCT o.ORD_NO), SUM(i.ORD_ITEM_QTY)
          FROM ORD o, ORD_ITEM i
         WHERE o.SHOP_NO = t.SHOP_NO
           AND o.ORD_DT  = t.ORD_DT
           AND i.ORD_NO  = o.ORD_NO)
 WHERE t.ORD_DT BETWEEN '20131201' AND '20131231'
   AND EXISTS (SELECT 1 FROM ORD o
                WHERE o.SHOP_NO = t.SHOP_NO AND o.ORD_DT = t.ORD_DT);

-- ② 없던 날은 넣는다
INSERT INTO SHOP_DAY (SHOP_NO, ORD_DT, ORD_CNT, ITEM_QTY)
SELECT o.SHOP_NO, o.ORD_DT, COUNT(DISTINCT o.ORD_NO), SUM(i.ORD_ITEM_QTY)
  FROM ORD o, ORD_ITEM i
 WHERE o.ORD_DT BETWEEN '20131201' AND '20131231'
   AND i.ORD_NO = o.ORD_NO
   AND NOT EXISTS (SELECT 1 FROM SHOP_DAY t
                    WHERE t.SHOP_NO = o.SHOP_NO AND t.ORD_DT = o.ORD_DT)
 GROUP BY o.SHOP_NO, o.ORD_DT;
COMMIT;

실행계획 학습용 예시

xplan

[① UPDATE 위 · ② INSERT 아래]
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT                         |              |      1 |        |      0 |00:00:06.02 |    420K |
|   1 |  UPDATE                                  | SHOP_DAY     |      1 |        |      0 |00:00:06.02 |    420K |
|*  2 |   HASH JOIN SEMI                         |              |      1 |   6200 |   6199 |00:00:00.03 |    1590 |
|*  3 |    TABLE ACCESS FULL                     | SHOP_DAY     |      1 |   6200 |   6200 |00:00:00.01 |     524 |
|   4 |    TABLE ACCESS BY INDEX ROWID BATCHED   | ORD          |      1 |  42408 |  42408 |00:00:00.02 |    1066 |
|*  5 |     INDEX RANGE SCAN                     | ORD_X01      |      1 |  42408 |  42408 |00:00:00.01 |     117 |
|   6 |   SORT AGGREGATE                         |              |   6199 |      1 |   6199 |00:00:05.81 |    409K |
|   7 |    NESTED LOOPS                          |              |   6199 |     17 |    104K|00:00:05.62 |    409K |
|*  8 |     TABLE ACCESS BY INDEX ROWID BATCHED  | ORD          |   6199 |      7 |  42347 |00:00:03.10 |    236K |
|*  9 |      INDEX RANGE SCAN                    | ORD_X01      |   6199 |   1370 |   8480K|00:00:01.02 |   43393 |
|  10 |     TABLE ACCESS BY INDEX ROWID BATCHED  | ORD_ITEM     |  42347 |      2 |    104K|00:00:02.41 |    174K |
|* 11 |      INDEX RANGE SCAN                    | ORD_ITEM_PK  |  42347 |      2 |    104K|00:00:01.20 |    127K |
-------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                         |              |      1 |        |      0 |00:00:02.10 |   56877 |
|   1 |  LOAD TABLE CONVENTIONAL                 | SHOP_DAY     |      1 |        |      0 |00:00:02.10 |   56877 |
|   2 |   HASH GROUP BY                          |              |      1 |   6200 |      3 |00:00:02.09 |   56855 |
|*  3 |    HASH JOIN                             |              |      1 |    104K|     53 |00:00:02.08 |   56855 |
|*  4 |     HASH JOIN ANTI                       |              |      1 |  42408 |     21 |00:00:00.03 |    1590 |
|   5 |      TABLE ACCESS BY INDEX ROWID BATCHED | ORD          |      1 |  42408 |  42408 |00:00:00.02 |    1066 |
|*  6 |       INDEX RANGE SCAN                   | ORD_X01      |      1 |  42408 |  42408 |00:00:00.01 |     117 |
|*  7 |      TABLE ACCESS FULL                   | SHOP_DAY     |      1 |   6200 |   6200 |00:00:00.01 |     524 |
|   8 |     TABLE ACCESS FULL                    | ORD_ITEM     |      1 |   2472K|   2472K|00:00:01.71 |   55265 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
① 2 - access("O"."SHOP_NO"="T"."SHOP_NO" AND "O"."ORD_DT"="T"."ORD_DT")
   3 - filter(("T"."ORD_DT">='20131201' AND "T"."ORD_DT"<='20131231'))
   5 - access("O"."ORD_DT">='20131201' AND "O"."ORD_DT"<='20131231')
   8 - filter("O"."SHOP_NO"=:B1)
   9 - access("O"."ORD_DT"=:B2)
  11 - access("I"."ORD_NO"="O"."ORD_NO")
② 3 - access("I"."ORD_NO"="O"."ORD_NO")
   4 - access("T"."SHOP_NO"="O"."SHOP_NO" AND "T"."ORD_DT"="O"."ORD_DT")
   6 - access("O"."ORD_DT">='20131201' AND "O"."ORD_DT"<='20131231')
   7 - filter(("T"."ORD_DT">='20131201' AND "T"."ORD_DT"<='20131231'))

  ① 갱신 6,199행 · ② 입력 3행 · redo 약 1.6 MB · SH0077 12월 24일 ORD_CNT 61 그대로

문제

지금 두 문장의 일량이 어디서 나오는지 실행계획의 Starts로 설명하고, SH0077의 12월 24일 행이 왜 그대로 남는지 설명하시오.
(A) 지금 방식, (B) MERGE 한 문장, (C) 12월분을 지우고 다시 넣기를 읽는 양, 고치는 행과 redo, 결과의 정확성, 매장 화면에 주는 영향으로 비교하시오.
고른 방법을 SQL로 작성하시오.

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

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

모범답안

모범 답안 SQL

sql

-- (B) MERGE 한 문장 · 12월 집계는 한 번만 · 주문이 사라진 날은 0으로 고친 뒤 지운다
MERGE INTO SHOP_DAY t
USING (SELECT NVL(a.SHOP_NO, d.SHOP_NO) AS SHOP_NO,
              NVL(a.ORD_DT,  d.ORD_DT)  AS ORD_DT,
              NVL(a.ORD_CNT,  0)        AS ORD_CNT,
              NVL(a.ITEM_QTY, 0)        AS ITEM_QTY
         FROM (SELECT o.SHOP_NO, o.ORD_DT,
                      COUNT(DISTINCT o.ORD_NO) AS ORD_CNT,
                      SUM(i.ORD_ITEM_QTY)      AS ITEM_QTY
                 FROM ORD o, ORD_ITEM i
                WHERE o.ORD_DT BETWEEN '20131201' AND '20131231'
                  AND i.ORD_DT BETWEEN '20131201' AND '20131231'   -- 복사해 둔 칸으로 ORD_ITEM_X01 범위
                  AND i.ORD_NO = o.ORD_NO
                GROUP BY o.SHOP_NO, o.ORD_DT) a
         FULL OUTER JOIN
              (SELECT SHOP_NO, ORD_DT
                 FROM SHOP_DAY
                WHERE ORD_DT BETWEEN '20131201' AND '20131231') d   -- 지금 있는 12월 행의 키
           ON d.SHOP_NO = a.SHOP_NO AND d.ORD_DT = a.ORD_DT) s
   ON (t.SHOP_NO = s.SHOP_NO AND t.ORD_DT = s.ORD_DT)
 WHEN MATCHED THEN UPDATE
      SET t.ORD_CNT = s.ORD_CNT, t.ITEM_QTY = s.ITEM_QTY
    WHERE t.ORD_CNT <> s.ORD_CNT OR t.ITEM_QTY <> s.ITEM_QTY        -- 바뀌는 행만 (둘 다 NOT NULL)
      DELETE WHERE t.ORD_CNT = 0                                    -- 주문이 사라진 날은 지운다
 WHEN NOT MATCHED THEN INSERT (SHOP_NO, ORD_DT, ORD_CNT, ITEM_QTY)
      VALUES (s.SHOP_NO, s.ORD_DT, s.ORD_CNT, s.ITEM_QTY);
COMMIT;

-- (C)를 고른다면 (12월분만 · 한 트랜잭션)
-- DELETE FROM SHOP_DAY WHERE ORD_DT BETWEEN '20131201' AND '20131231';
-- INSERT INTO SHOP_DAY (SHOP_NO, ORD_DT, ORD_CNT, ITEM_QTY)
-- SELECT o.SHOP_NO, o.ORD_DT, COUNT(DISTINCT o.ORD_NO), SUM(i.ORD_ITEM_QTY)
--   FROM ORD o, ORD_ITEM i
--  WHERE o.ORD_DT BETWEEN '20131201' AND '20131231'
--    AND i.ORD_DT BETWEEN '20131201' AND '20131231'
--    AND i.ORD_NO = o.ORD_NO
--  GROUP BY o.SHOP_NO, o.ORD_DT;
-- COMMIT;

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

xplan

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT                            |              |      1 |        |      0 |00:00:00.31 |    5262 |
|   1 |  MERGE                                     | SHOP_DAY     |      1 |        |      0 |00:00:00.31 |    5262 |
|   2 |   VIEW                                     |              |      1 |        |   6203 |00:00:00.29 |    4922 |
|*  3 |    HASH JOIN OUTER                         |              |      1 |   6203 |   6203 |00:00:00.29 |    4922 |
|   4 |     VIEW                                   |              |      1 |   6203 |   6203 |00:00:00.27 |    4398 |
|   5 |      VIEW                                  | VW_FOJ_0     |      1 |   6203 |   6203 |00:00:00.27 |    4398 |
|*  6 |       HASH JOIN FULL OUTER                 |              |      1 |   6203 |   6203 |00:00:00.27 |    4398 |
|   7 |        VIEW                                |              |      1 |   6200 |   6202 |00:00:00.25 |    3874 |
|   8 |         HASH GROUP BY                      |              |      1 |   6200 |   6202 |00:00:00.25 |    3874 |
|*  9 |          HASH JOIN                         |              |      1 |    104K|    104K|00:00:00.18 |    3874 |
|  10 |           TABLE ACCESS BY INDEX ROWID BATCHED| ORD        |      1 |  42408 |  42408 |00:00:00.02 |    1066 |
|* 11 |            INDEX RANGE SCAN                | ORD_X01      |      1 |  42408 |  42408 |00:00:00.01 |     117 |
|  12 |           TABLE ACCESS BY INDEX ROWID BATCHED| ORD_ITEM   |      1 |    104K|    104K|00:00:00.06 |    2808 |
|* 13 |            INDEX RANGE SCAN                | ORD_ITEM_X01 |      1 |    104K|    104K|00:00:00.02 |     401 |
|* 14 |        TABLE ACCESS FULL                   | SHOP_DAY     |      1 |   6200 |   6200 |00:00:00.01 |     524 |
|  15 |     TABLE ACCESS FULL                      | SHOP_DAY     |      1 |    146K|    146K|00:00:00.01 |     524 |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("T"."SHOP_NO"="S"."SHOP_NO" AND "T"."ORD_DT"="S"."ORD_DT")
   6 - access("D"."SHOP_NO"="A"."SHOP_NO" AND "D"."ORD_DT"="A"."ORD_DT")
   9 - access("I"."ORD_NO"="O"."ORD_NO")
  11 - access("O"."ORD_DT">='20131201' AND "O"."ORD_DT"<='20131231')
  13 - access("I"."ORD_DT">='20131201' AND "I"."ORD_DT"<='20131231')
  14 - filter(("ORD_DT">='20131201' AND "ORD_DT"<='20131231'))

  갱신 215행(숫자가 바뀐 214 + 0건이 된 1) · 그중 1행 삭제 · 입력 3행 · 바뀌지 않은 5,985행은 그대로 · redo 약 0.1 MB

[세 방법 비교 — 같은 12월분 (학습용 예시)]
  방법                                  Buffers   고친 행                         redo      SH0077 12월 24일
  (A) 지금: UPDATE + INSERT              477K      갱신 6,199 · 입력 3              약 1.6 MB  61건 그대로
  (B) MERGE 한 문장 (FULL OUTER)          5,262     갱신 214 · 삭제 1 · 입력 3        약 0.1 MB  지워짐
  (C) 12월분 DELETE + INSERT              약 54K    삭제 6,200 · 입력 6,202           약 3.4 MB  지워짐

모범 답안 서술

(A)의 비용은 UPDATE의 SET 서브쿼리에서 나온다. SHOP_DAY 12월분 6,199행마다 그 매장 그날의 주문을 다시 집계한다(①의 6번 줄 Starts 6,199). 한 번에 ORD_X01로 그날 주문 약 1,370건을 훑어 매장 조건으로 7건을 거르고(8번과 9번 줄), 그 주문의 상품을 ORD_ITEM_PK로 찾는다(10번과 11번 줄 Starts 42,347). 매장 일 하나에 약 66블록이고, 6,199번이면 409K다. ②의 INSERT는 ORD_ITEM에 날짜 조건이 없어서 2,472,717행을 통째로 읽는다(8번 줄 55,265블록). 두 문장 합계가 약 477K다. 한 번이면 될 12월 집계를 매장 일마다 6,199번, INSERT에서 또 한 번 하는 셈이다. SH0077의 12월 24일이 남는 것은 UPDATE의 EXISTS 때문이다. 주문이 있는 날만 고치므로 주문이 0건이 된 날은 UPDATE가 고르지 않고, INSERT도 이미 있는 행이라 건너뛴다. 어느 문장도 그 행을 건드리지 않아 61건이 그대로 남는다. 세 방법을 견주면 이렇다. (A)는 477K를 읽고 6,199행을 매달 다시 쓰며(redo 약 1.6MB), 주문이 사라진 날을 남긴다. (B) MERGE는 12월 집계를 한 번만 하고(3,874블록) 바뀐 행만 고친다. 다만 MERGE는 원천과 맞물린 대상 행만 건드리므로, 원천을 집계 결과만으로 만들면 (A)와 똑같이 SH0077 행을 남긴다. 그래서 집계 결과와 SHOP_DAY 12월분 키를 FULL OUTER JOIN해 원천에 넣고, 주문이 없어진 날은 0으로 고친 뒤 DELETE WHERE로 지운다. Buffers 5,262, 고친 행 215(삭제 1), 입력 3, redo 약 0.1MB다. (C)는 가장 단순하고 사라진 날도 자연히 정리된다. 대신 지우고 다시 넣으면서 바뀌지 않은 5,985행까지 테이블과 PK 인덱스를 두 번 고치므로 redo가 약 3.4MB로 가장 많고, 지우는 동안 그 행들을 잠근다. 매장 화면은 읽기 일관성 덕분에 커밋 전까지 옛 값을 보므로 빈 화면을 보지는 않는다. 하지만 TRUNCATE나 파티션 비우기로 바꾸면 그 순간 커밋되어 빈 집계가 보인다. 모범 답안은 (B)다. 매달 반복되는 배치이고, 바뀌는 행이 한 달 6,200행 가운데 200여 행이라 바뀐 것만 고치는 쪽이 redo와 잠금을 가장 적게 남긴다. 원천의 GROUP BY 키가 ON 키(SHOP_NO, ORD_DT)와 같으므로 대상 한 행에 원천 한 행이 보장돼 ORA-30926도 나지 않는다. 집계에서 ORD_ITEM은 복사해 둔 ORD_DT로 범위를 잡아 ORD_ITEM_X01을 쓴다.

해설

MERGE의 DELETE 절은 WHEN MATCHED로 UPDATE된 행 가운데 조건에 맞는 행만 지우고, 조건은 고친 뒤의 값으로 따진다. 원천에 없는 대상 행은 지울 수 없다. 그래서 대상 키를 FULL OUTER JOIN으로 원천에 넣었다.
(B)를 더 단순하게: 집계 결과만으로 MERGE하고 DELETE FROM SHOP_DAY t WHERE t.ORD_DT BETWEEN … AND NOT EXISTS (SELECT 1 FROM ORD o WHERE o.SHOP_NO = t.SHOP_NO AND o.ORD_DT = t.ORD_DT)를 따로 돌려도 된다. 문장이 둘이 되고 ORD를 한 번 더 읽는다(약 1,600블록).
(C)가 맞는 경우: 바뀌는 행이 대부분이거나 집계 정의가 자주 바뀌는 테이블이라면 (C)가 단순하고 실수가 적다. SHOP_DAY를 월 파티션으로 두면 새 집계를 다른 테이블에 만들어 파티션 교환으로 바꿔 끼울 수도 있다(PART 단원). 교환은 순간이지만 DDL이므로 진행 중인 조회와 잠금에 주는 영향을 먼저 본다.
비교에 쓰는 <>: ORD_CNT, ITEM_QTY가 NOT NULL이고 원천도 NVL로 0을 채웠으니 <>로 충분하다. NULL이 올 수 있다면 DECODE로 비교한다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황