SQLP 튜닝 연습장

테마

튜닝 연습DML과 배치 튜닝
DML-021

매장 통폐합 MERGE가 ORA-38104로 멈추자 폐점 매장마다 UPDATE를 한 번씩 돌린다

기초UPDATE버거446 / 700

상황 ​

버거원 본사가 매장 6곳을 가까운 매장에 흡수시켰다. 과거 주문도 흡수한 매장의 실적으로 보기로 해서 ORD.SHOP_NO를 바꿔야 한다. 개발자가 매핑 테이블로 MERGE를 짰더니 ORA-38104가 났고, 그래서 매장마다 UPDATE를 한 줄씩 쓰는 스크립트로 바꿨다. 리허설에서 여섯 문장에 40초가 걸렸다. 반영 창은 일요일 새벽 30분이고, 앞으로 분기마다 통폐합이 있다.

테이블과 인덱스 ​

  • ORD 주문: 1,000,000행 · 22,422블록 · 매장 200개(매장당 약 5,000건) · 날짜순으로 적재돼 한 매장의 주문이 모든 블록에 흩어져 있다
  • 인덱스는 ORD_PK (ORD_NO)와 ORD_X01 (ORD_DT, ORD_HMS)이다. SHOP_NO에는 인덱스가 없다.
  • SHOP_MAP(이번 통폐합 매핑): 6행 · OLD_SHOP_NO(폐점 매장), NEW_SHOP_NO(흡수한 매장) · PK (OLD_SHOP_NO) · 흡수한 매장은 OLD_SHOP_NO에 다시 나오지 않는다
  • 폐점 6곳의 주문은 29,874건이다. ORD를 참조하는 FK와 트리거는 없다.

원본 SQL ​

sql
-- 처음 짠 MERGE → ORA-38104: Columns referenced in the ON Clause cannot be updated: "O"."SHOP_NO"
-- MERGE INTO ORD o
-- USING SHOP_MAP m
--    ON (o.SHOP_NO = m.OLD_SHOP_NO)
--  WHEN MATCHED THEN UPDATE SET o.SHOP_NO = m.NEW_SHOP_NO;

-- 그래서 매장마다 한 문장씩
UPDATE ORD SET SHOP_NO = 'SH0045' WHERE SHOP_NO = 'SH0137';
UPDATE ORD SET SHOP_NO = 'SH0012' WHERE SHOP_NO = 'SH0088';
UPDATE ORD SET SHOP_NO = 'SH0101' WHERE SHOP_NO = 'SH0154';
UPDATE ORD SET SHOP_NO = 'SH0023' WHERE SHOP_NO = 'SH0171';
UPDATE ORD SET SHOP_NO = 'SH0067' WHERE SHOP_NO = 'SH0190';
UPDATE ORD SET SHOP_NO = 'SH0118' WHERE SHOP_NO = 'SH0196';
COMMIT;

실행계획 학습용 예시 ​

xplan
[리허설 — 문장 하나 (SH0137 → SH0045) · 여섯 문장 모두 같은 모양]
-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |      |      1 |        |      0 |00:00:06.71 |   30117 |
|   1 |  UPDATE            | ORD  |      1 |        |      0 |00:00:06.71 |   30117 |
|*  2 |   TABLE ACCESS FULL| ORD  |      1 |   5000 |   4987 |00:00:06.12 |   22422 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("SHOP_NO"='SH0137')

[리허설 — 여섯 문장 합계]
  경과 시간    40.3초 (대부분 ORD 전체 스캔의 물리 읽기)
  Buffers      약 180K (전체 스캔 22,422 × 6 = 134,532 + 갱신 약 45K)
  갱신된 행    29,874

문제 ​

  1. 처음 짠 MERGE가 ORA-38104로 멈춘 이유를 설명하시오.
  2. 여섯 문장의 일량을 실행계획으로 설명하고, 매핑 테이블을 써서 한 문장으로 다시 작성하시오. 그 문장이 안전하려면 매핑 테이블에 무엇이 보장돼야 하는지도 쓰시오.
원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.
무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자