정산 확정 MERGE에서 ORA-38104를 피하려고 조건을 뺐더니 확정된 정산까지 다시 고쳐진다

중급MERGE한빛유통453 / 700

상황

정산 원장 T_ORD_STLM에는 확정 칸 STLM_ST가 있다('N' 미확정, 'Y' 확정). 매일 23:00 배치가 미확정 정산의 금액과 정산일을 주문의 결제 금액과 결제일로 채우고 확정한다. 처음 짠 MERGE는 ON 절에 s.STLM_ST = 'N'을 넣었다가 ORA-38104로 멈췄고, 개발자가 그 조건을 빼서 돌아가게 만들었다. 그 뒤로 배치는 매일 10만 건을 모두 고친다. 그리고 이번 달 재무팀 대사에서, 지난달에 확정한 정산 37건의 금액이 확정 뒤에 바뀐 것이 드러났다. 정산팀 조민우: "확정된 정산은 금액이 바뀌면 안 돼요. 확정 뒤 결제 정정은 보정 이력으로 따로 남기거든요."

테이블과 인덱스

T_ORD_STLM(정산 원장): 100,000행 · 640블록 · 인덱스 없음 · ORD_SEQ로 T_ORD와 1:1 · 이 문제에서는 STLM_ST CHAR(1) 칸이 더 있다('Y' 98,760 · 'N' 1,240)
T_ORD: 100,000행 · 896블록 · PK_T_ORD (ORD_SEQ) · PAY_AMT, PAY_DT
통계는 최신이다.

원본 SQL

sql

-- 처음 판:  ON (s.ORD_SEQ = o.ORD_SEQ AND s.STLM_ST = 'N')
--   → ORA-38104: Columns referenced in the ON Clause cannot be updated: "S"."STLM_ST"
-- 지금 판 (ON 절에서 STLM_ST 조건을 뺐다)
MERGE INTO T_ORD_STLM s
USING T_ORD o
   ON (s.ORD_SEQ = o.ORD_SEQ)
 WHEN MATCHED THEN UPDATE
      SET s.STLM_AMT = o.PAY_AMT,
          s.STLM_DT  = o.PAY_DT,
          s.STLM_ST  = 'Y';

실행계획 학습용 예시

xplan

[세션 통계 — 어제 23:00] 갱신된 행 100,000 (그중 미확정 1,240) · redo 약 27 MB · 확정 정산 가운데 금액이 바뀐 행 37

-------------------------------------------------------------------------------------------
| Id  | Operation             | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT       |            |      1 |        |      0 |00:00:01.21 |    103K |
|   1 |  MERGE                | T_ORD_STLM |      1 |        |      0 |00:00:01.21 |    103K |
|   2 |   VIEW                |            |      1 |        |    100K|00:00:00.08 |    1540 |
|*  3 |    HASH JOIN          |            |      1 |    100K|    100K|00:00:00.07 |    1540 |
|   4 |     TABLE ACCESS FULL | T_ORD_STLM |      1 |    100K|    100K|00:00:00.01 |     644 |
|   5 |     TABLE ACCESS FULL | T_ORD      |      1 |    100K|    100K|00:00:00.01 |     896 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("S"."ORD_SEQ"="O"."ORD_SEQ")

문제

처음 판이 ORA-38104로 멈춘 이유를 설명하시오.
지금 판이 무엇을 망가뜨렸는지 실행계획과 숫자로 설명하시오.
미확정 정산만 고치도록 다시 작성하고, 그 고침이 처음 판과 같은 뜻이 되는 조건을 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

MERGE INTO T_ORD_STLM s
USING T_ORD o
   ON (s.ORD_SEQ = o.ORD_SEQ)                -- ON에는 '어느 행이 짝인가'만
 WHEN MATCHED THEN UPDATE
      SET s.STLM_AMT = o.PAY_AMT,
          s.STLM_DT  = o.PAY_DT,
          s.STLM_ST  = 'Y'
    WHERE s.STLM_ST = 'N';                   -- '그중 무엇을 고칠까'는 여기에. 확정분은 건드리지 않는다

-- 같은 답 (조인 뷰 · PK_T_ORD 덕분에 T_ORD_STLM이 키 보존 테이블)
-- UPDATE (SELECT s.STLM_AMT, s.STLM_DT, s.STLM_ST, o.PAY_AMT, o.PAY_DT
--           FROM T_ORD_STLM s, T_ORD o
--          WHERE o.ORD_SEQ = s.ORD_SEQ AND s.STLM_ST = 'N')
--    SET STLM_AMT = PAY_AMT, STLM_DT = PAY_DT, STLM_ST = 'Y';

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

xplan

[세션 통계] 갱신된 행 1,240 · redo 약 0.3 MB · 확정 정산 변경 0 · 한 번 더 돌리면 0건

-------------------------------------------------------------------------------------------
| Id  | Operation             | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT       |            |      1 |        |      0 |00:00:00.09 |    3400 |
|   1 |  MERGE                | T_ORD_STLM |      1 |        |      0 |00:00:00.09 |    3400 |
|   2 |   VIEW                |            |      1 |        |    100K|00:00:00.08 |    1540 |
|*  3 |    HASH JOIN          |            |      1 |    100K|    100K|00:00:00.07 |    1540 |
|   4 |     TABLE ACCESS FULL | T_ORD_STLM |      1 |    100K|    100K|00:00:00.01 |     644 |
|   5 |     TABLE ACCESS FULL | T_ORD      |      1 |    100K|    100K|00:00:00.01 |     896 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("S"."ORD_SEQ"="O"."ORD_SEQ")

모범 답안 서술

MERGE는 ON 절의 결과로 대상 행이 원천과 '맞는지'를 가르고, 맞는 행에는 UPDATE를, 맞지 않는 행에는 INSERT를 한다. 처음 판은 ON 절에 s.STLM_ST = 'N'을 두고 SET에서 STLM_ST를 'Y'로 바꿨다. 맞는지를 정한 칸을 같은 문장이 고치면 실행 도중에 그 행의 판정이 뒤집힐 수 있으므로, 오라클은 ON 절에 쓴 칸을 SET에서 고치지 못하게 막는다. 그것이 ORA-38104다. 지금 판은 ON 절에서 조건을 빼면서 '미확정만'이라는 뜻까지 지웠다. 해시 조인(3번 줄)으로 10만 건이 전부 맞물리고, 확정된 98,760건까지 매일 다시 고친다. 1번 줄 Buffers 103K의 대부분이 그 갱신이고 redo도 약 27MB 생긴다. 더 큰 문제는 결과다. 확정 뒤 결제 정정이 들어온 37건의 금액이 확정 정산 위에 덮였고, 확정 정산은 바뀌면 안 된다는 재무 규정이 깨졌다. 대상 칸 조건은 WHEN MATCHED THEN UPDATE 뒤의 WHERE에 둔다. WHERE s.STLM_ST = 'N'이면 미확정 1,240건만 고치고 확정분은 건드리지 않는다. 고친 행은 100,000 → 1,240, Buffers는 103K → 약 3,400으로 준다. 한 번 더 돌려도 미확정이 없으니 0건이다. 조인 뷰 UPDATE의 WHERE에 같은 조건을 두거나(PK_T_ORD 덕분에 T_ORD_STLM이 키 보존 테이블이다), 원천에 미확정 정산의 ROWID를 담아 맞추는 MERGE도 같은 답이다. 이 고침이 처음 판과 같은 뜻인 것은 이 MERGE에 WHEN NOT MATCHED THEN INSERT가 없기 때문이다. INSERT 절이 있었다면, 처음 판에서는 확정 정산이 '맞지 않는 행'이 되어 INSERT 쪽으로 가고 고친 판에서는 '맞는 행'이 되어 아무것도 하지 않는다. 조건을 ON에서 WHERE로 옮길 때는 INSERT 절이 있는지 먼저 본다.

해설

ON 절에는 '어느 행이 짝인가'만, WHERE에는 '그중 무엇을 고칠까'를 둔다. 짝을 정하는 키와 고칠 조건을 섞으면 이번처럼 ORA-38104를 만나거나, INSERT 절이 있을 때 이미 있는 행을 한 번 더 넣는 MERGE가 된다.
다른 정답: USING (SELECT s2.ROWID AS rid, o.PAY_AMT, o.PAY_DT FROM T_ORD_STLM s2 JOIN T_ORD o ON o.ORD_SEQ = s2.ORD_SEQ WHERE s2.STLM_ST = 'N') x ON (s.ROWID = x.rid)처럼 원천에서 미확정만 고르는 MERGE도 된다. ON 절에 STLM_ST가 없으니 ORA-38104가 나지 않는다.
확정 칸이 있는 원장을 고치는 배치는 '확정되지 않은 것만'이라는 조건을 반드시 가져야 한다. 조건이 있으면 재실행도 안전하다(두 번째 실행은 0건).
이미 덮인 37건은 원래 확정 금액을 되살려야 한다. 원장 이력이나 보정 이력이 없다면 언두 보존 기간 안에서 Flashback Query(AS OF TIMESTAMP)로 확정 시점 값을 읽을 수 있는지 먼저 본다. 기간을 넘었으면 백업에서 찾아야 한다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황