POS 재전송분 적재가 가끔 ORA-00001로 멈추고, 멈추지 않는 날에도 새 주문을 두 번 쓴다

중급MERGE버거451 / 700

상황

버거원 본사는 매일 04:00에 매장 POS가 보낸 주문 파일을 ORD_STG에 받아 ORD에 반영한다. 파일에는 새 주문과, 매장이 정정해 다시 보낸 주문(재전송)이 섞여 있다. 지금 배치는 새 주문을 INSERT한 다음, 파일에 있는 주문을 모두 UPDATE한다. 한 달에 두세 번은 POS 재시도로 같은 주문이 한 파일에 두 번 들어와 INSERT가 ORA-00001로 멈춘다. 그러면 아침에 운영 담당이 중복 행을 손으로 지우고 다시 돌린다.

테이블과 인덱스

ORD 주문: 1,000,000행 · 22,422블록 · ORD_PK (ORD_NO) 높이 3 · ORD_X01 (ORD_DT, ORD_HMS)
ORD_STG(적재용, 날마다 비우고 받는다): 34블록 · ORD_NO, ORD_DT, ORD_HMS, SHOP_NO, RCV_SEQ(수신 순번) · 제약과 인덱스는 없다
오늘 파일 1,452행: 새 주문 1,371건 + 재전송 79건(그중 값이 실제로 바뀐 것 64건) + 재시도 중복 2행(새 주문 2건이 두 번씩 들어왔다)
같은 주문이 두 번 오면 나중에 받은 것(RCV_SEQ가 큰 쪽)이 맞다. 세 칸(ORD_DT, ORD_HMS, SHOP_NO)은 NOT NULL이다.

원본 SQL

sql

-- ① 새 주문
INSERT INTO ORD (ORD_NO, ORD_DT, ORD_HMS, SHOP_NO)
SELECT s.ORD_NO, s.ORD_DT, s.ORD_HMS, s.SHOP_NO
  FROM ORD_STG s
 WHERE NOT EXISTS (SELECT 1 FROM ORD o WHERE o.ORD_NO = s.ORD_NO);

-- ② 정정 (파일에 있는 주문은 모두)
UPDATE ORD o
   SET (o.ORD_DT, o.ORD_HMS, o.SHOP_NO) =
       (SELECT s.ORD_DT, s.ORD_HMS, s.SHOP_NO FROM ORD_STG s WHERE s.ORD_NO = o.ORD_NO)
 WHERE o.ORD_NO IN (SELECT s.ORD_NO FROM ORD_STG s);
COMMIT;

실행계획 학습용 예시

xplan

[중복이 없던 어제 · 1,450행 파일 — ① INSERT 위 · ② UPDATE 아래]
---------------------------------------------------------------------------------------------
| Id  | Operation                | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |         |      1 |        |      0 |00:00:00.08 |   10566 |
|   1 |  LOAD TABLE CONVENTIONAL | ORD     |      1 |        |      0 |00:00:00.08 |   10566 |
|   2 |   NESTED LOOPS ANTI      |         |      1 |   1450 |   1371 |00:00:00.01 |    4384 |
|   3 |    TABLE ACCESS FULL     | ORD_STG |      1 |   1450 |   1450 |00:00:00.01 |      34 |
|*  4 |    INDEX UNIQUE SCAN     | ORD_PK  |   1450 |      1 |     79 |00:00:00.01 |    4350 |
---------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT         |         |      1 |        |      0 |00:00:00.24 |   58075 |
|   1 |  UPDATE                  | ORD     |      1 |        |      0 |00:00:00.24 |   58075 |
|   2 |   NESTED LOOPS           |         |      1 |   1450 |   1450 |00:00:00.01 |    4384 |
|   3 |    SORT UNIQUE           |         |      1 |   1450 |   1450 |00:00:00.01 |      34 |
|   4 |     TABLE ACCESS FULL    | ORD_STG |      1 |   1450 |   1450 |00:00:00.01 |      34 |
|*  5 |    INDEX UNIQUE SCAN     | ORD_PK  |   1450 |      1 |   1450 |00:00:00.01 |    4350 |
|*  6 |   TABLE ACCESS FULL      | ORD_STG |   1450 |      1 |   1450 |00:00:00.05 |   49300 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   ① 4 - access("O"."ORD_NO"="S"."ORD_NO")
   ② 5 - access("O"."ORD_NO"="S"."ORD_NO")
      6 - filter("S"."ORD_NO"=:B1)

[중복 2행이 있던 오늘 — ① INSERT]
  ORA-00001: unique constraint (DEV.ORD_PK) violated   → 문장 전체가 되돌려지고 배치가 멈췄다

문제

오늘 INSERT가 ORA-00001로 멈춘 이유를 설명하시오. 중복이 정정 주문에 생겼다면 UPDATE는 어떻게 되는지도 쓰시오.
멈추지 않은 어제의 두 문장이 한 일을 실행계획으로 설명하시오. 특히 UPDATE의 A-Rows 1,450이 무엇인지 설명하시오.
한 문장으로 다시 작성하시오. 재시도 중복은 나중에 받은 것을 쓰고, 값이 바뀐 정정만 고치게 하시오.

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

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

모범답안

모범 답안 SQL

sql

MERGE INTO ORD o
USING (SELECT ORD_NO, ORD_DT, ORD_HMS, SHOP_NO
         FROM (SELECT s.ORD_NO, s.ORD_DT, s.ORD_HMS, s.SHOP_NO,
                      ROW_NUMBER() OVER (PARTITION BY s.ORD_NO ORDER BY s.RCV_SEQ DESC) AS rn
                 FROM ORD_STG s)
        WHERE rn = 1) s                               -- 재시도 중복은 나중에 받은 것 하나만
   ON (o.ORD_NO = s.ORD_NO)
 WHEN MATCHED THEN UPDATE                             -- 정정: 값이 바뀐 것만
      SET o.ORD_DT = s.ORD_DT, o.ORD_HMS = s.ORD_HMS, o.SHOP_NO = s.SHOP_NO
    WHERE DECODE(o.ORD_DT,  s.ORD_DT,  0, 1) = 1
       OR DECODE(o.ORD_HMS, s.ORD_HMS, 0, 1) = 1
       OR DECODE(o.SHOP_NO, s.SHOP_NO, 0, 1) = 1
 WHEN NOT MATCHED THEN INSERT (ORD_NO, ORD_DT, ORD_HMS, SHOP_NO)     -- 새 주문
      VALUES (s.ORD_NO, s.ORD_DT, s.ORD_HMS, s.SHOP_NO);
COMMIT;

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

xplan

---------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT                |         |      1 |        |      0 |00:00:00.07 |   10825 |
|   1 |  MERGE                         | ORD     |      1 |        |      0 |00:00:00.07 |   10825 |
|   2 |   VIEW                         |         |      1 |        |   1450 |00:00:00.02 |    4463 |
|   3 |    NESTED LOOPS OUTER          |         |      1 |   1452 |   1450 |00:00:00.02 |    4463 |
|*  4 |     VIEW                       |         |      1 |   1452 |   1450 |00:00:00.01 |      34 |
|*  5 |      WINDOW SORT PUSHED RANK   |         |      1 |   1452 |   1452 |00:00:00.01 |      34 |
|   6 |       TABLE ACCESS FULL        | ORD_STG |      1 |   1452 |   1452 |00:00:00.01 |      34 |
|   7 |     TABLE ACCESS BY INDEX ROWID| ORD     |   1450 |      1 |     79 |00:00:00.01 |    4429 |
|*  8 |      INDEX UNIQUE SCAN         | ORD_PK  |   1450 |      1 |     79 |00:00:00.01 |    4350 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("RN"=1)
   5 - filter(ROW_NUMBER() OVER ( PARTITION BY "S"."ORD_NO" ORDER BY INTERNAL_FUNCTION("S"."RCV_SEQ") DESC )<=1)
   8 - access("O"."ORD_NO"="S"."ORD_NO")

[오늘 파일 1,452행] 새 주문 1,371건 INSERT · 정정 64건 UPDATE(값이 같은 재전송 15건은 건너뜀) · 중복 2행은 나중 것 하나만 · 멈춤 0

모범 답안 서술

NOT EXISTS는 문장이 시작될 때의 ORD를 본다. 오늘 파일에는 새 주문 두 건이 두 행씩 들어 있었고, 두 행 모두 'ORD에 없다'를 통과해 함께 INSERT되다가 둘째 행이 ORD_PK에 걸려 ORA-00001이 났다. 문장 전체가 되돌려지므로 새 주문이 하나도 들어가지 않고 배치가 멈춘다. 중복이 정정 주문에 생겼다면 ②의 SET 서브쿼리가 두 행을 돌려줘 ORA-01427로 멈춘다. 멈추지 않은 날에도 일이 두 배다. ②가 돌 때는 ①이 넣은 새 주문 1,371건이 이미 ORD에 있어서, IN (SELECT … FROM ORD_STG)가 파일의 1,450건을 모두 고른다(②의 2번 줄 A-Rows 1,450). 방금 넣은 행을 같은 값으로 다시 고치는 것이다. 그리고 고치는 행마다 SET 서브쿼리가 인덱스 없는 ORD_STG 34블록을 처음부터 읽는다(6번 줄 Starts 1,450, 49,300블록). ORD_STG를 세 번 읽는 셈이고, 두 문장의 Buffers 합계는 약 68,600이다. 고친 배치는 MERGE 한 문장이다. 원천에서 ROW_NUMBER() OVER (PARTITION BY ORD_NO ORDER BY RCV_SEQ DESC)로 주문마다 나중에 받은 한 행만 남기고, ON으로 맞는 주문(정정)과 맞지 않는 주문(새 주문)을 한 번에 가른다. 정정은 WHEN MATCHED의 WHERE로 값이 바뀐 64건만 고친다. Buffers는 약 68,600 → 10,825, 고친 행은 1,450 → 64로 줄고, 중복이 와도 멈추지 않는다. 원천을 먼저 한 행으로 줄이는 일은 MERGE에서도 반드시 필요하다. 줄이지 않으면 새 주문 중복은 똑같이 ORA-00001로, 정정 중복은 ORA-30926으로 멈춘다.

해설

순서만 바꾸면? UPDATE를 먼저, INSERT를 나중에 돌리면 새 주문을 두 번 쓰는 일은 없어진다. 그래도 ORD_STG를 행마다 다시 읽는 일과 중복 문제는 남는다.
중복을 고를지 멈출지: 재시도 중복은 '나중에 받은 것이 맞다'는 업무 규칙이 있으니 원천에서 정리한다. 규칙이 없는 중복(같은 주문 번호에 다른 매장)이라면 조용히 고르지 말고 반송함에 남긴 뒤 멈추는 쪽이 맞다(교재 15화의 반송함).
LOG ERRORS로 ORA-00001 행만 따로 빼는 방법도 있지만 집합 처리가 행 단위로 바뀌어 느려지고(교재 15화), 중복 가운데 어느 행이 남을지 정할 수 없다.
세 칸이 NOT NULL이라 정정 비교는 <>로 써도 된다. 모범 답안은 칸이 NULL을 허용하게 바뀌어도 견디도록 DECODE로 비교했다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황