LOG ERRORS를 붙여 둔 제휴 포인트 적재가 중복 3건 때문에 통째로 롤백됐다

중급INSERT이커머스465 / 700

이 문제는 교재 18화 졸업 과제와 같은 이커머스 데이터베이스를 씁니다. 『튜너의 1년』을 순서대로 읽고 있다면 졸업 과제를 먼저 풀고 오세요. 아래 「테이블과 인덱스」 칸에 과제의 답 일부(제약 조건, 인덱스, 통계의 실제 상태)가 나옵니다.

상황

이커머스 쇼핑몰은 매일 03:00에 제휴사가 보낸 포인트 적립 파일을 stg_point_tx로 받아 point_transactions에 넣는다. 문제가 있는 행은 에러 테이블에 남기고 나머지는 넣도록 LOG ERRORS를 붙여 두었다. 10월 28일 제휴사가 전날 파일의 일부를 한 번 더 보냈고, 적재는 ORA-00001로 실패했다. 들어간 행도, 에러 테이블에 남은 행도 0건이었다. 고객센터: "어제 제휴 적립이 하나도 안 됐다는 문의가 오전에만 40건 들어왔어요." 앱은 낮에도 적립과 사용을 point_transactions에 한 건씩 넣는다.

테이블과 인덱스

point_transactions: 45,000행 · 210블록 · PK tx_id(PK_POINT_TRANSACTIONS) · tx_type (EARN · USE · EXPIRE) · 앱이 시간당 약 400건을 넣는다(새벽에도 약 50건).
제휴 적립은 제휴사 거래 번호를 그대로 tx_id로 쓴다.
stg_point_tx: 날마다 비우고 받는다. 10월 28일 파일은 1,214행이고, 그중 3행은 point_transactions에 이미 있는 tx_id다(제휴사가 한 번 더 보낸 행). 파일 안에서 겹치는 행은 없다.
에러 테이블 err$_point_transactions는 DBMS_ERRLOG.CREATE_ERROR_LOG로 만들었다.

원본 SQL

sql

-- 매일 03:00 · 제휴사 포인트 적립
INSERT /*+ APPEND */ INTO point_transactions (tx_id, customer_id, amount, tx_type, created_at)
SELECT s.tx_id, s.customer_id, s.amount, 'EARN', s.created_at
  FROM stg_point_tx s
   LOG ERRORS INTO err$_point_transactions ('20241028') REJECT LIMIT UNLIMITED;
COMMIT;

실행계획 학습용 예시

xplan

[10/28 03:00 배치 로그]
  ORA-00001: unique constraint (ECOMMERCE.PK_POINT_TRANSACTIONS) violated
  point_transactions 들어간 행 0 · err$_point_transactions ('20241028') 0행 · 문장 전체 롤백

[실행계획]
---------------------------------------------------------------------------------------------------
| Id  | Operation          | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT   |                    |      1 |        |      0 |00:00:00.04 |      61 |
|   1 |  LOAD AS SELECT    | POINT_TRANSACTIONS |      1 |        |      0 |00:00:00.04 |      61 |
|   2 |   TABLE ACCESS FULL| STG_POINT_TX       |      1 |   1214 |   1214 |00:00:00.01 |       7 |
---------------------------------------------------------------------------------------------------

[10/12 · 비슷한 일이 있던 날 운영 담당이 힌트를 지우고 손으로 다시 돌린 결과]
  1,209행 적재 · err$_point_transactions 5행 (ORA-00001) · 커밋

문제

LOG ERRORS를 붙였는데도 중복 3행 때문에 문장 전체가 롤백된 이유를 설명하시오. 10월 12일에는 왜 됐는지도 쓰시오.
중복은 에러 테이블에 남기고 나머지는 넣도록 적재를 고치시오. 넣은 건수와 거부된 건수가 파일 건수와 맞는지 배치가 스스로 확인하게 하시오.
이 테이블에 직접 경로 적재를 쓰지 않는 다른 이유를 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 매일 03:00 · 제휴사 포인트 적립 — 일반 INSERT 로 (중복은 에러 테이블로 · TM 3 이라 앱의 적립과 함께 돈다)
DECLARE
  v_tag VARCHAR2(8) := TO_CHAR(SYSDATE, 'YYYYMMDD');
  v_ins NUMBER;
  v_err NUMBER;
  v_src NUMBER;
BEGIN
  DELETE FROM err$_point_transactions WHERE ora_err_tag$ = v_tag;     -- 다시 돌릴 때를 위해 회차 것을 비운다

  INSERT INTO point_transactions (tx_id, customer_id, amount, tx_type, created_at)
  SELECT s.tx_id, s.customer_id, s.amount, 'EARN', s.created_at
    FROM stg_point_tx s
     LOG ERRORS INTO err$_point_transactions (v_tag) REJECT LIMIT UNLIMITED;
  v_ins := SQL%ROWCOUNT;

  SELECT COUNT(*) INTO v_err FROM err$_point_transactions WHERE ora_err_tag$ = v_tag;
  SELECT COUNT(*) INTO v_src FROM stg_point_tx;

  -- 대조: 넣은 행 + 거부된 행 = 파일 행 (1,211 + 3 = 1,214)
  IF v_ins + v_err <> v_src THEN
    ROLLBACK;
    RAISE_APPLICATION_ERROR(-20040, '제휴 적립 대조 불일치: 파일 ' || v_src || ' / 적재 ' || v_ins || ' / 거부 ' || v_err);
  END IF;
  COMMIT;
END;

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

xplan

[10/28 파일로 다시 돌림]
---------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |                    |      1 |        |      0 |00:00:00.06 |    3684 |
|   1 |  LOAD TABLE CONVENTIONAL | POINT_TRANSACTIONS |      1 |        |      0 |00:00:00.06 |    3684 |
|   2 |   TABLE ACCESS FULL      | STG_POINT_TX       |      1 |   1214 |   1214 |00:00:00.01 |       7 |
---------------------------------------------------------------------------------------------------------

  넣은 행 1,211 · err$_point_transactions 3행 (ORA-00001 · 제휴사가 한 번 더 보낸 tx_id) · 1,211 + 3 = 1,214 · 커밋
  V$LOCK   TM LMODE 3 (앱의 새벽 적립 INSERT 가 기다리지 않는다)

모범 답안 서술

직접 경로 적재(실행계획 1번 줄 LOAD AS SELECT)는 행을 넣을 때마다 PK를 검사하지 않는다. 블록을 고수위선 위에 통째로 쓴 뒤, 적재 끝에 넣은 키를 정렬해 PK 인덱스에 한꺼번에 반영하면서 유니크를 검사한다. 그래서 어느 행이 중복인지 행 단위로 가려 에러 테이블에 적을 수 없다. 오라클은 직접 경로 INSERT의 유니크 제약, 유니크 인덱스 위반을 DML 오류 로깅(LOG ERRORS)이 받지 못하는 경우로 정해 두었고, 그때는 문장 전체가 실패하고 롤백된다. 그래서 중복 3행 때문에 1,211행까지 함께 사라졌고 에러 테이블도 0행이었다. 10월 12일에 된 것은 운영 담당이 힌트를 지워 일반 INSERT로 돌렸기 때문이다. 일반 INSERT는 행마다 PK를 검사하므로 LOG ERRORS가 중복 행만 에러 테이블에 적고 나머지를 넣는다. 고친 적재는 APPEND를 빼고 일반 INSERT에 LOG ERRORS를 붙인다. 회차 태그(v_tag)로 에러 테이블을 나누고, 넣은 행(SQL%ROWCOUNT)과 거부된 행(그 회차의 에러 테이블 건수)을 더한 값이 파일 건수와 같은지 대조한다(1,211 + 3 = 1,214). 맞지 않으면 롤백하고 에러로 멈춘다. 에러 테이블 행은 자율 트랜잭션으로 들어가 본 문장을 롤백해도 남으므로, 다시 돌릴 때를 위해 처음에 그 회차 행을 지운다. 직접 경로를 쓰지 않을 이유는 하나 더 있다. 앱이 새벽에도 이 테이블에 적립을 넣는다. 직접 경로 적재는 커밋할 때까지 테이블에 TM 6(배타)을 걸어 그동안 앱의 INSERT를 기다리게 한다. 1,214건 정도에서는 직접 경로의 이득도 거의 없다. 일반 INSERT는 TM 3이라 앱과 함께 돈다. 이미 있는 tx_id를 NOT EXISTS로 미리 거르면 APPEND를 남길 수도 있다. 하지만 거른 행이 사유와 함께 어디에도 남지 않아, 제휴사가 같은 거래를 왜 두 번 보냈는지 나중에 알 수 없다. 거른 행을 따로 반송 테이블에 넣어야 같은 정보가 남는다.

해설

DML 오류 로깅이 받지 못하는 경우는 문서에 정해져 있다. 직접 경로 INSERT와 MERGE의 유니크 제약 또는 유니크 인덱스 위반, UPDATE와 MERGE의 유니크 위반, 지연 제약 위반은 기록되지 않고 문장이 실패한다. 'LOG ERRORS를 붙였으니 안전하다'고 믿기 전에 적재 방식부터 본다.
LOG ERRORS는 오류가 하나도 없어도 집합 처리를 행 단위로 바꿔 느려진다(교재 15화 실측 23 → 105 ~ 117cs). 1,214건이면 문제 되지 않는 크기지만, 수십만 건이라면 알고 있는 실패는 조건으로 먼저 거르는 쪽이 낫다.
제휴사가 같은 파일을 한 번 더 보내는 일이 잦다면 tx_id 중복은 '오류'가 아니라 '이미 처리됨'이다. 그때는 MERGE의 WHEN NOT MATCHED만 쓰거나 NOT EXISTS로 거르고, 거른 건수를 따로 세어 대조에 넣는다.
REJECT LIMIT UNLIMITED는 오류가 몇 건이든 나머지를 넣는다. 파일이 통째로 잘못 온 날도 '성공'이 되므로, 거부 건수가 일정 비율을 넘으면 멈추는 기준을 함께 둔다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황