매장 하나의 시각을 정정했는데 104,864건이 갱신됐다

중급MERGE버거427 / 700

상황

무대는 버거원 POS 본사 DB다. SH0137 매장 POS의 시계가 2013년 3월 한 달 동안 1시간 늦어서, 실제보다 1시간 이른 시각이 찍혔다. 주문(ORD)의 ORD_HMS는 본사가 이미 1시간씩 더해 정정했다(3월 31일 23시대에 찍힌 몇 건은 ORD_DT도 4월 1일로 바꿨다). 주문상품(ORD_ITEM)에는 같은 두 칸이 복사돼 있어서(반정규화) 그것도 맞춰야 한다. 개발팀이 넘긴 SQL을 반영 리허설(운영 복제본)에서 돌렸더니 8초가 걸렸고 104,864건이 갱신됐다. 테스트 DB에는 SH0137의 3월 주문만 넣어 두고 시험해서 건수를 확인하지 못했다.

테이블과 인덱스

ORD_ITEM 주문상품: 2,472,717행 · 55,265블록 · 2013년 3월분 104,864행. ORD_DT와 ORD_HMS는 NULL을 허용한다(NOT NULL 제약 없음).
인덱스 ORD_ITEM_PK (ORD_NO, ITEM_ID) · ORD_ITEM_X01 (ORD_DT, ORD_HMS)
ORD 주문: 1,000,000행 · 22,422블록 · ORD_PK (ORD_NO) 유일 · ORD_X01 (ORD_DT, ORD_HMS) · 200개 매장
정정된 SH0137 주문은 212건(정정 뒤 날짜 3/1 ~ 4/1)이고, 그 주문상품은 524행이다. 통계는 최신이다.

원본 SQL

sql

UPDATE ORD_ITEM i
   SET i.ORD_DT  = (SELECT o.ORD_DT  FROM ORD o
                     WHERE o.ORD_NO = i.ORD_NO AND o.SHOP_NO = 'SH0137'),
       i.ORD_HMS = (SELECT o.ORD_HMS FROM ORD o
                     WHERE o.ORD_NO = i.ORD_NO AND o.SHOP_NO = 'SH0137')
 WHERE i.ORD_DT BETWEEN '20130301' AND '20130331';

실행계획 학습용 예시

xplan

Plan hash value: 1880412637

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT                      |              |      1 |        |      0 |00:00:08.12 |   1214K |
|   1 |  UPDATE                               | ORD_ITEM     |      1 |        |      0 |00:00:08.12 |   1214K |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED | ORD_ITEM     |      1 |    106K|    104K|00:00:00.21 |    2812 |
|*  3 |    INDEX RANGE SCAN                   | ORD_ITEM_X01 |      1 |    106K|    104K|00:00:00.04 |     402 |
|*  4 |   TABLE ACCESS BY INDEX ROWID         | ORD          |    104K|      1 |    524 |00:00:00.61 |    319K |
|*  5 |    INDEX UNIQUE SCAN                  | ORD_PK       |    104K|      1 |    104K|00:00:00.28 |    214K |
|*  6 |   TABLE ACCESS BY INDEX ROWID         | ORD          |    104K|      1 |    524 |00:00:00.58 |    319K |
|*  7 |    INDEX UNIQUE SCAN                  | ORD_PK       |    104K|      1 |    104K|00:00:00.27 |    214K |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("I"."ORD_DT">='20130301' AND "I"."ORD_DT"<='20130331')
   4 - filter("O"."SHOP_NO"='SH0137')
   5 - access("O"."ORD_NO"=:B1)
   6 - filter("O"."SHOP_NO"='SH0137')
   7 - access("O"."ORD_NO"=:B1)

[리허설 뒤 — 같은 복제본에서 센 것]
  갱신된 행                              104,864
  ORD_DT · ORD_HMS 가 NULL 이 된 행      104,340
  redo size                              약 61 MB

문제

Buffers 1,214K가 어디서 나오는지 실행계획의 Starts로 설명하시오.
리허설 뒤 104,340행에 무슨 일이 일어났는지, 원본 SQL의 어느 부분 때문인지 설명하시오.
ORD를 한 번 조인해서 정정된 주문의 주문상품만 고치는 SQL로 다시 작성하고, 그 SQL이 안전하려면 무엇이 보장돼야 하는지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 정정된 주문(SH0137 · 정정 뒤 3/1 ~ 4/1)에서 출발해 그 주문상품만 한 번에 고친다
MERGE INTO ORD_ITEM i
USING (SELECT ORD_NO, ORD_DT, ORD_HMS
         FROM ORD
        WHERE SHOP_NO = 'SH0137'
          AND ORD_DT BETWEEN '20130301' AND '20130401') o
   ON (i.ORD_NO = o.ORD_NO)
 WHEN MATCHED THEN UPDATE
      SET i.ORD_DT = o.ORD_DT, i.ORD_HMS = o.ORD_HMS
    WHERE i.ORD_DT <> o.ORD_DT OR i.ORD_HMS <> o.ORD_HMS;   -- 이미 같은 행은 건너뛴다

-- 같은 답 (수정 가능 조인 뷰 — ORD_PK 가 유일해서 ORD_ITEM 이 키 보존 테이블)
-- UPDATE (SELECT i.ORD_DT AS i_dt, i.ORD_HMS AS i_hms, o.ORD_DT AS o_dt, o.ORD_HMS AS o_hms
--           FROM ORD_ITEM i, ORD o
--          WHERE i.ORD_NO = o.ORD_NO
--            AND o.SHOP_NO = 'SH0137' AND o.ORD_DT BETWEEN '20130301' AND '20130401')
--    SET i_dt = o_dt, i_hms = o_hms;

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

xplan

Plan hash value: 2730518864

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT                         |             |      1 |        |      0 |00:00:00.04 |    5122 |
|   1 |  MERGE                                  | ORD_ITEM    |      1 |        |      0 |00:00:00.04 |    5122 |
|   2 |   VIEW                                  |             |      1 |        |    541 |00:00:00.02 |    1978 |
|   3 |    NESTED LOOPS                         |             |      1 |    545 |    541 |00:00:00.02 |    1978 |
|*  4 |     TABLE ACCESS BY INDEX ROWID BATCHED | ORD         |      1 |    219 |    219 |00:00:00.01 |    1102 |
|*  5 |      INDEX RANGE SCAN                   | ORD_X01     |      1 |  43776 |  43776 |00:00:00.01 |     121 |
|   6 |     TABLE ACCESS BY INDEX ROWID BATCHED | ORD_ITEM    |    219 |      2 |    541 |00:00:00.01 |     876 |
|*  7 |      INDEX RANGE SCAN                   | ORD_ITEM_PK |    219 |      2 |    541 |00:00:00.01 |     657 |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("SHOP_NO"='SH0137')
   5 - access("ORD_DT">='20130301' AND "ORD_DT"<='20130401')
   7 - access("I"."ORD_NO"="ORD_NO")

  갱신 524 행 (4/1 정상 주문 17 행은 값이 같아 건너뜀) · 다시 돌리면 0 행 · NULL 이 된 행 0

모범 답안 서술

원본은 ORD_ITEM의 3월분 104,864행을 전부 갱신 대상으로 잡고(3번 줄 access), 행마다 SET의 서브쿼리 두 개가 ORD를 따로 찾아간다. 4번과 6번 줄에 Starts 104K가 두 번 나오는 것이 그 흔적이다. 서브쿼리 하나가 ORD_PK와 테이블에서 약 3블록을 읽으므로 둘이면 638K이고, 여기에 10만 행을 고치는 몫(키 칸이 바뀌니 ORD_ITEM_X01도 고친다)이 더해져 Buffers가 1,214K가 된다. 더 큰 문제는 결과다. 매장 조건 SHOP_NO = 'SH0137'이 서브쿼리 안에만 있고 바깥 WHERE에는 없다. 그래서 다른 199개 매장의 3월 주문상품 104,340행은 서브쿼리가 행을 못 찾아 NULL을 돌려받았고, ORD_DT와 ORD_HMS가 NULL로 덮였다(4번 줄 A-Rows 524, 찾은 것은 SH0137뿐이다). 칸이 NULL을 허용하므로 에러도 나지 않는다. MERGE로 바꿔 정정된 주문(ORD의 SH0137, 3/1 ~ 4/1, 219건)에서 출발하고, ORD_ITEM_PK로 그 주문의 주문상품만 찾아 한 번에 고친다. 원천에 없는 행은 아예 건드리지 않는다. 값이 이미 같은 행(4/1 정상 주문 17행)은 WHEN MATCHED의 WHERE로 건너뛰므로 다시 돌려도 0건이다. 갱신은 104,864 → 524행, Buffers는 1,214K → 약 5,100으로 준다. 이 SQL이 안전하려면 원천(ORD)이 대상 행마다 하나여야 한다. ORD_NO가 ORD_PK로 유일하므로 주문상품 한 행에 맞는 주문은 하나다(겹치면 MERGE는 ORA-30926으로 멈춘다). 조인 뷰 UPDATE로 쓸 때도 같은 이유로 ORD_ITEM이 키 보존 테이블이 된다(그렇지 않으면 ORA-01779).

해설

함정: SET 칸 = (서브쿼리)는 '찾은 행만'이 아니라 바깥 WHERE에 걸린 행 전부를 고친다. 못 찾으면 NULL이 들어간다. 원본 모양을 지키려면 WHERE EXISTS (같은 서브쿼리)를 붙여야 하고, 그래도 행마다 서브쿼리는 남는다(이번에는 세 번).
SET (ORD_DT, ORD_HMS) = (SELECT ORD_DT, ORD_HMS …)로 묶으면 서브쿼리가 두 번에서 한 번으로 준다. 그래도 대상 행 수(104,864)와 NULL 함정은 그대로다.
반영 전에 건수부터 본다. SELECT COUNT(*)로 대상이 524인지 104,864인지 보는 것이 가장 싼 검증이다. 테스트 DB에 정정 대상만 넣어 두면 '바깥 행'이 없어서 이 함정이 보이지 않는다(교재 08화 체크리스트 E, 1 쪽에 없는 키).
반정규화의 대가: 원천(ORD)을 고칠 때마다 사본(ORD_ITEM)을 같이 고쳐야 하고, 그 칸이 인덱스(ORD_ITEM_X01)의 키라면 인덱스도 고쳐야 한다(교재 14화, DML의 부수비용).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황