DW 전송 표시 한 칸을 고쳤을 뿐인데 판매상품 정정 이력이 110만 행 늘었다

기초DDL유통691 / 700

상황

본사 유통은 2019년 회계 감사 지적 뒤로, 판매상품의 수량이나 단가를 정정하면 정정 전후 값을 CF11_HIS에 남긴다. 이 일은 트리거 TRG_CF11_HIS가 한다. 이번 달 DW 서버를 바꾸면서 판매상품 전체를 다시 보내려고 전송 표시(DW_SND_YN)를 110만 행 모두 'N'으로 고쳤다. UPDATE 한 문장이 41초 걸렸고 CF11_HIS가 1,096,708행 늘었다. 감사팀이 물었다. "정정이 110만 건이라는데 무슨 일인가요?"

테이블과 인덱스

CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO) · SLE_QTY와 SLE_UPRC는 NOT NULL
DW_SND_YN CHAR(1)(이 문제용): DW로 보낸 행은 'Y'다. 밤마다 DW 전송 작업이 새 판매상품 3,000줄 안팎을 보내고 'Y'로 고친다.
CF11_HIS 판매상품 정정 이력(이 문제용): HIS_DT · SLE_NO · ITM_NO · OLD_QTY · NEW_QTY · OLD_UPRC · NEW_UPRC · 인덱스 없음 · 이번 일 전에 3,212,560행이었다
ERP의 「판매상품 정정」 화면은 한 줄을 저장할 때 SLE_QTY, SLE_UPRC, DW_SND_YN을 모두 SET에 넣는다. 정정한 줄은 DW로 다시 보내야 해서 'N'으로 되돌린다. 정정은 한 달에 수십 건이다.

원본 SQL

sql

-- 2019년 트리거
CREATE OR REPLACE TRIGGER TRG_CF11_HIS
AFTER UPDATE ON CF11
FOR EACH ROW
BEGIN
  INSERT INTO CF11_HIS (HIS_DT, SLE_NO, ITM_NO, OLD_QTY, NEW_QTY, OLD_UPRC, NEW_UPRC)
  VALUES (SYSDATE, :OLD.SLE_NO, :OLD.ITM_NO, :OLD.SLE_QTY, :NEW.SLE_QTY, :OLD.SLE_UPRC, :NEW.SLE_UPRC);
END;
/

-- DW 서버 교체 · 전체 재전송 표시
UPDATE CF11 SET DW_SND_YN = 'N';
COMMIT;

실행계획 학습용 예시

xplan

[세션 통계 — UPDATE CF11 SET DW_SND_YN = 'N' (1,096,708행)]
                               운영 (트리거 있음)     시험 DB (트리거 없음)
  경과 시간                         41.6 초                6.3 초
  recursive calls                1,097,215                   86
  execute count                  1,097,190                    9
  session logical reads          2,391,206            1,127,904
  redo size                         486 MB               214 MB
  CF11_HIS 증가                  1,096,708행                  —

[TKPROF — 트리거 안의 INSERT (dep=1)]
INSERT INTO CF11_HIS (HIS_DT, SLE_NO, ITM_NO, OLD_QTY, NEW_QTY, OLD_UPRC, NEW_UPRC)
VALUES (SYSDATE, :B6, :B5, :B4, :B3, :B2, :B1)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute 1096708    24.10      26.92          0       5834    1152630     1096708

[CF11_HIS — 이번 일 전 3,212,560행]
  OLD_QTY <> NEW_QTY 또는 OLD_UPRC <> NEW_UPRC 인 행             2,140
  두 값이 모두 같은 행 (밤마다 DW 전송 표시를 고칠 때 생긴 행)  3,210,420

문제

전송 표시 한 칸을 고친 UPDATE가 41초 걸리고 정정 이력이 110만 행 늘어난 이유를 설명하시오.
수량이나 단가가 실제로 바뀐 행만 이력에 남도록 트리거를 고치시오. 정정 화면처럼 칸을 모두 SET에 넣는 UPDATE도 고려하시오.
이미 쌓인 이력은 어떻게 할지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 수량 · 단가 칸이 SET에 있을 때만, 그리고 값이 실제로 바뀐 행만 (두 칸 모두 NOT NULL)
CREATE OR REPLACE TRIGGER TRG_CF11_HIS
AFTER UPDATE OF SLE_QTY, SLE_UPRC ON CF11
FOR EACH ROW
WHEN (NEW.SLE_QTY <> OLD.SLE_QTY OR NEW.SLE_UPRC <> OLD.SLE_UPRC)
BEGIN
  INSERT INTO CF11_HIS (HIS_DT, SLE_NO, ITM_NO, OLD_QTY, NEW_QTY, OLD_UPRC, NEW_UPRC)
  VALUES (SYSDATE, :OLD.SLE_NO, :OLD.ITM_NO, :OLD.SLE_QTY, :NEW.SLE_QTY, :OLD.SLE_UPRC, :NEW.SLE_UPRC);
END;
/

-- ② 쌓인 이력 (감사팀 확인 뒤) — 4,309,268행 중 진짜 정정 2,140행만 옮겨 남긴다
CREATE TABLE CF11_HIS_KEEP AS
SELECT *
  FROM CF11_HIS
 WHERE OLD_QTY <> NEW_QTY OR OLD_UPRC <> NEW_UPRC;

-- 건수(2,140)를 대조한 뒤 이름을 바꾼다 · 옛 테이블은 감사팀이 확인할 때까지 둔다
ALTER TABLE CF11_HIS RENAME TO CF11_HIS_OLD;
ALTER TABLE CF11_HIS_KEEP RENAME TO CF11_HIS;
-- 트리거는 다음에 돌 때 새 CF11_HIS로 다시 컴파일된다 · 권한(GRANT)은 다시 준다

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

xplan

[세션 통계 — 같은 UPDATE (고친 트리거)]
  경과 6.4 초 · recursive calls 88 · execute count 9
  session logical reads 1,128,015 · redo size 214 MB
  CF11_HIS 증가 0행 (UPDATE OF에 없는 칸이라 트리거가 아예 돌지 않는다)

[정정 화면 시험 — 세 칸을 모두 SET에 넣는 저장]
  수량 3 → 5로 고친 줄           CF11_HIS 1행
  아무것도 안 바꾸고 저장한 줄    CF11_HIS 0행 (WHEN이 거짓이라 트리거 본문을 부르지 않는다)

[이력 정리]  CF11_HIS 4,309,268행 → 2,140행

모범 답안 서술

트리거 선언이 AFTER UPDATE ON CF11 FOR EACH ROW뿐이라, 어느 칸을 고치든 UPDATE된 행마다 트리거가 돈다. 전송 표시만 고친 UPDATE도 1,096,708행 모두에서 트리거가 돌아 이력을 INSERT했다. TKPROF의 트리거 안 INSERT가 Execute 1,096,708이고, 세션 통계의 recursive calls 1,097,215도 같은 숫자다. 행마다 PL/SQL을 부르고 이력 한 행을 넣느라 경과가 6.3초(시험 DB) → 41.6초, redo가 214 → 486MB가 됐다. 이력에는 수량과 단가가 정정 전후로 똑같은 행이 110만 개 생겼다. 밤마다 DW 전송 작업이 3,000줄씩 표시를 고칠 때도 같은 일이 있었다. 그래서 이력 3,212,560행 가운데 진짜 정정은 2,140행뿐이었다. 트리거는 두 겹으로 고친다. ① UPDATE OF SLE_QTY, SLE_UPRC로 수량이나 단가 칸이 SET에 있는 UPDATE에만 돌게 한다. 전송 표시만 고치는 UPDATE에서는 트리거가 아예 돌지 않는다. ② 그런데 UPDATE OF는 그 칸이 SET에 있으면 값이 같아도 돈다. 정정 화면은 세 칸을 모두 SET에 넣으므로, WHEN (NEW.SLE_QTY <> OLD.SLE_QTY OR NEW.SLE_UPRC <> OLD.SLE_UPRC)로 값이 실제로 바뀐 행만 고른다. WHEN 절은 SQL 엔진이 행마다 평가하고, 거짓이면 트리거 본문(PL/SQL)을 부르지 않는다. 두 칸이 NOT NULL이라 <>로 충분하다. 고친 뒤 같은 UPDATE는 41.6초 → 6.4초, recursive calls는 1,097,215 → 88이 되고 이력은 한 행도 늘지 않는다. 정정 화면에서 수량을 바꾼 줄은 이력이 한 행 남고, 아무것도 바꾸지 않고 저장한 줄은 남지 않는다. 쌓인 이력은 감사팀과 확인한 뒤 걷어 낸다. 4,309,268행 가운데 남길 행이 2,140행이므로, 430만 행을 DELETE하며 undo와 redo를 만드는 것보다 남길 행만 CTAS로 옮기고 이름을 바꾸는 쪽이 싸다. 옛 테이블은 감사팀이 확인할 때까지 이름만 바꿔 둔다.

해설

함정: UPDATE OF만 붙이면 DW 표시 문제는 풀리지만 정정 화면의 "값은 그대로인 저장"은 여전히 이력을 남긴다. 반대로 WHEN만 붙이면 결과는 맞지만, 전송 표시를 고치는 110만 행마다 트리거가 불려 WHEN을 평가한다. 칸으로 한 번, 값으로 한 번 거른다.
칸이 NULL을 받을 수 있다면 <>는 NULL과의 비교에서 UNKNOWN이 되어 정정을 놓친다. 그때는 DECODE(NEW.c, OLD.c, 0, 1) = 1처럼 NULL도 같은 값으로 보는 비교를 쓴다.
트리거 본문 안에서 IF로 걸러도 결과는 같다. 하지만 행마다 PL/SQL 본문을 부르는 비용은 남는다. 거를 수 있는 것은 선언(UPDATE OF, WHEN)에서 거른다.
DW 전송 표시 같은 운영용 칸을 업무 테이블에 두면 이런 일이 되풀이된다. 전송 여부를 따로 관리하는 테이블로 빼는 것도 방법이다. 다만 전송 작업도 함께 고쳐야 한다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황