PART-023

재전송 주문 MERGE를 빠르게 고친 뒤 같은 주문번호가 두 달 칸에 하나씩 생겼다

중급MERGE버거398 / 700

상황

매장 POS는 통신 장애가 나면 그날 주문을 밤에 다시 보낸다. 이미 받은 주문이 대부분이고, 처음 오는 주문과 매장 시계 보정으로 주문 시각이 바뀐 주문이 섞여 있다. 본사 야간 배치가 이 재전송분을 MERGE로 주문 테이블에 반영한다. 석 달 전 조민우가 MERGE를 고쳤다. 원래 문장은 한 번에 5만 블록을 넘게 읽었고, 월말 경계의 주문이 오면 가끔 ORA-14402로 멈췄다. 고친 뒤로는 0.1초에 끝나고 에러도 없다. 그런데 이번 분기 매출 대사에서 같은 주문번호가 6월 칸과 7월 칸에 하나씩 있는 주문 12건이 나왔다.

테이블과 인덱스

ORD_RANGE 주문(파티션 복사본, 교재 14화): 1,000,000행 · PARTITION BY RANGE (ORD_YM) 월별 25칸(P201201 ~ P201312과 빈 P_DEFAULT). 칸 하나 FULL ≈ 1,980블록이다. 행 이동(ROW MOVEMENT)은 꺼져 있다(기본값).
ORD_YM은 SUBSTR(ORD_DT, 1, 6)으로 채우는 파티션 키다. 주문번호 ORD_NO는 본사가 매장에 나눠 준 번호라 전 기간에서 유일해야 한다.
LOCAL 인덱스 ORD_RANGE_PK (ORD_NO, ORD_YM)(칸마다 높이 2) · ORD_RANGE_X01 (ORD_DT, ORD_HMS). GLOBAL 인덱스는 없다.
ORD_RESEND 재전송 스테이징(문제 설정): 하룻밤 약 3,000행 · 22블록 · ORD_NO · ORD_DT · ORD_HMS · ORD_YM · SHOP_NO. 이날 3,000건 중 2,950건은 같은 달로 이미 받은 주문, 38건은 처음 오는 주문, 12건은 6월 30일 23시 59분대 주문이 시계 보정으로 7월 1일 0시대로 바뀌어 온 주문이다.
매월 1일 보관 기간이 지난 칸을 DROP PARTITION으로 정리한다.

원본 SQL

sql

-- 석 달 전에 고친 MERGE (지금)
MERGE INTO ORD_RANGE t
USING ORD_RESEND s
   ON (t.ORD_NO = s.ORD_NO AND t.ORD_YM = s.ORD_YM)
 WHEN MATCHED THEN UPDATE
      SET t.ORD_DT = s.ORD_DT, t.ORD_HMS = s.ORD_HMS, t.SHOP_NO = s.SHOP_NO
 WHEN NOT MATCHED THEN INSERT (ORD_NO, ORD_DT, ORD_HMS, ORD_YM, SHOP_NO)
      VALUES (s.ORD_NO, s.ORD_DT, s.ORD_HMS, s.ORD_YM, s.SHOP_NO);

-- 원래 문장의 ON 과 SET (석 달 전까지)
--   ON (t.ORD_NO = s.ORD_NO)
--   SET t.ORD_DT = s.ORD_DT, t.ORD_HMS = s.ORD_HMS, t.ORD_YM = s.ORD_YM, t.SHOP_NO = s.SHOP_NO

실행계획 학습용 예시 · 교재 14화 규모

xplan

[원래 문장 · 석 달 전 · 1.12초 · 가끔 ORA-14402: updating partition key column would cause a partition change]
------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name       | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
------------------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT         |            |      1 |        |      0 | 00:00:01.12 |   51012 |        |       |
|   1 |  MERGE                  | ORD_RANGE  |      1 |        |      0 | 00:00:01.12 |   51012 |        |       |
|   2 |   VIEW                  |            |      1 |        |   3000 | 00:00:01.01 |   47482 |        |       |
|*  3 |    HASH JOIN OUTER      |            |      1 |   3000 |   3000 | 00:00:01.01 |   47482 |        |       |
|   4 |     TABLE ACCESS FULL   | ORD_RESEND |      1 |   3000 |   3000 | 00:00:00.01 |      22 |        |       |
|   5 |     PARTITION RANGE ALL |            |      1 |  1000K |  1000K | 00:00:00.62 |   47460 |      1 |    25 |
|   6 |      TABLE ACCESS FULL  | ORD_RANGE  |     24 |  1000K |  1000K | 00:00:00.48 |   47460 |      1 |    25 |
------------------------------------------------------------------------------------------------------------------
   3 - access("T"."ORD_NO"(+)="S"."ORD_NO")

[지금 · 0.11초 · 에러 없음]
-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name         | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT                        |              |      1 |        |      0 | 00:00:00.11 |   12188 |        |       |
|   1 |  MERGE                                 | ORD_RANGE    |      1 |        |      0 | 00:00:00.11 |   12188 |        |       |
|   2 |   VIEW                                 |              |      1 |        |   3000 | 00:00:00.06 |    8972 |        |       |
|   3 |    NESTED LOOPS OUTER                  |              |      1 |   3000 |   3000 | 00:00:00.06 |    8972 |        |       |
|   4 |     TABLE ACCESS FULL                  | ORD_RESEND   |      1 |   3000 |   3000 | 00:00:00.01 |      22 |        |       |
|   5 |     PARTITION RANGE SINGLE             |              |   3000 |      1 |   2950 | 00:00:00.05 |    8950 |    KEY |   KEY |
|   6 |      TABLE ACCESS BY LOCAL INDEX ROWID | ORD_RANGE    |   3000 |      1 |   2950 | 00:00:00.04 |    8950 |    KEY |   KEY |
|*  7 |       INDEX UNIQUE SCAN                | ORD_RANGE_PK |   3000 |      1 |   2950 | 00:00:00.02 |    6000 |    KEY |   KEY |
-----------------------------------------------------------------------------------------------------------------------------------
   7 - access("T"."ORD_NO"="S"."ORD_NO" AND "T"."ORD_YM"="S"."ORD_YM")

[대사]
  SELECT ORD_NO, COUNT(*), MIN(ORD_YM), MAX(ORD_YM) FROM ORD_RANGE GROUP BY ORD_NO HAVING COUNT(*) > 1;
  → 12행 · 모두 201306 / 201307 · 7월 칸 쪽은 재전송 MERGE 가 INSERT 한 행이다

문제

고친 MERGE가 빨라진 까닭과, 같은 주문번호가 두 칸에 하나씩 생긴 까닭을 ON 절과 PK 정의로 설명하시오. PK가 왜 막지 못했는지도 쓰시오.
빠르면서도 주문번호가 하나만 남도록 MERGE와 인덱스를 고치고, 이미 생긴 12건을 정리하는 SQL을 작성하시오. 원래 문장이 ORA-14402로 멈추던 문제도 함께 해결하시오.
그렇게 고쳤을 때의 부작용을 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- (1) 이미 생긴 12건 : 시계가 보정되어 다시 온 쪽(7월 칸)을 남기고 옛 칸(6월)의 행을 지운다
DELETE FROM ORD_RANGE t
 WHERE (t.ORD_NO, t.ORD_YM) IN (SELECT ORD_NO, MIN(ORD_YM)
                                  FROM ORD_RANGE
                                 GROUP BY ORD_NO
                                HAVING COUNT(*) > 1);
COMMIT;

-- (2) 주문번호 하나의 유일성은 칸과 상관없는 GLOBAL 유니크 인덱스가 지킨다
CREATE UNIQUE INDEX ORD_RANGE_GX01 ON ORD_RANGE (ORD_NO);

-- (3) 월이 바뀐 주문은 행이 다른 칸으로 옮겨 가야 한다 (없으면 ORA-14402)
ALTER TABLE ORD_RANGE ENABLE ROW MOVEMENT;

-- (4) 맞추는 조건은 주문번호 하나 · 월은 고치는 값
MERGE INTO ORD_RANGE t
USING ORD_RESEND s
   ON (t.ORD_NO = s.ORD_NO)
 WHEN MATCHED THEN UPDATE
      SET t.ORD_DT = s.ORD_DT, t.ORD_HMS = s.ORD_HMS, t.ORD_YM = s.ORD_YM, t.SHOP_NO = s.SHOP_NO
 WHEN NOT MATCHED THEN INSERT (ORD_NO, ORD_DT, ORD_HMS, ORD_YM, SHOP_NO)
      VALUES (s.ORD_NO, s.ORD_DT, s.ORD_HMS, s.ORD_YM, s.SHOP_NO);
COMMIT;

-- (5) 월 정리 : 이제 글로벌 인덱스가 있으므로 같이 고친다
ALTER TABLE ORD_RANGE DROP PARTITION P201201 UPDATE GLOBAL INDEXES;

개선 후 실행계획 학습용 예시 · 교재 14화 규모

xplan

[고친 MERGE · 같은 3,000행 · 0.13초]
-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name           | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT                        |                |      1 |        |      0 | 00:00:00.13 |   15310 |        |       |
|   1 |  MERGE                                 | ORD_RANGE      |      1 |        |      0 | 00:00:00.13 |   15310 |        |       |
|   2 |   VIEW                                 |                |      1 |        |   3000 | 00:00:00.07 |   11984 |        |       |
|   3 |    NESTED LOOPS OUTER                  |                |      1 |   3000 |   3000 | 00:00:00.07 |   11984 |        |       |
|   4 |     TABLE ACCESS FULL                  | ORD_RESEND     |      1 |   3000 |   3000 | 00:00:00.01 |      22 |        |       |
|   5 |     TABLE ACCESS BY GLOBAL INDEX ROWID | ORD_RANGE      |   3000 |      1 |   2962 | 00:00:00.06 |   11962 |  ROWID | ROWID |
|*  6 |      INDEX UNIQUE SCAN                 | ORD_RANGE_GX01 |   3000 |      1 |   2962 | 00:00:00.03 |    9000 |        |       |
-------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   6 - access("T"."ORD_NO"="S"."ORD_NO")

  맞은 행 2,962 (2,950 + 칸이 바뀐 12 · 6월 칸에서 7월 칸으로 옮겨 갔다) · INSERT 38 · 오류 없음
  대사 쿼리 → 0행

모범 답안 서술

지금 문장이 빠른 까닭은 ON 절에 파티션 키가 들어 있기 때문이다. t.ORD_YM = s.ORD_YM이 있으면 행마다 칸 하나를 골라(PARTITION RANGE SINGLE, KEY) ORD_RANGE_PK (ORD_NO, ORD_YM)를 INDEX UNIQUE SCAN으로 한 번에 찾는다(약 9,000블록, 0.11초). 원래 문장은 ON에 주문번호뿐이어서, 이 PK(Nonprefixed LOCAL)로는 칸마다 뒤져야 했으므로 옵티마이저가 24칸을 모두 FULL로 읽어 해시 조인했다(47,460블록, 1.12초). 그런데 ON에 파티션 키를 넣으면 '월이 바뀌어 온 주문'은 기존 행과 맞지 않는다. 6월 30일 23시 59분대 주문이 시계 보정으로 7월 1일로 바뀌어 오면 s.ORD_YM은 '201307'이고, 6월 칸의 기존 행은 '201306'이라 맞을 행이 없다. 그래서 MERGE는 이 12건을 처음 오는 주문으로 보고 7월 칸에 INSERT했다. PK는 이것을 막지 못했다. ORD_RANGE_PK는 LOCAL로 만들려고 파티션 키를 붙인 (ORD_NO, ORD_YM)이라, 지키는 것은 쌍의 유일성이고 ORD_YM이 다르면 같은 주문번호도 통과한다. 원래 문장이 가끔 ORA-14402로 멈춘 것도 같은 12건 때문이다. 주문번호로 맞춘 뒤 ORD_YM을 바꾸면 행이 다른 칸으로 가야 하는데 행 이동이 꺼져 있었다. 조민우는 ORD_YM을 ON으로 옮겨 에러와 느림을 함께 없앴지만, 대신 에러가 날 자리에서 중복이 조용히 생기게 했다. 고치는 방향은 "맞추는 조건은 주문번호 하나, 월은 고치는 값"이다. 먼저 12건을 정리한다(정정되어 다시 온 7월 칸 행을 남기고 6월 칸 행을 지운다). 그다음 칸과 상관없는 GLOBAL 유니크 인덱스 ORD_RANGE_GX01 (ORD_NO)를 만든다. 이 인덱스가 주문번호 하나의 유일성을 지키고, ON을 (t.ORD_NO = s.ORD_NO)로 되돌려도 MERGE가 행마다 인덱스를 한 번에 찾는다(약 12,000블록, 0.13초). 마지막으로 ENABLE ROW MOVEMENT를 켜서, 월이 바뀐 12건은 UPDATE 때 6월 칸에서 7월 칸으로 옮겨 가게 한다. ORA-14402도 중복도 없어진다. 부작용은 셋이다. 이 테이블에 글로벌 인덱스가 생겼으므로 월 정리 DROP PARTITION에 UPDATE GLOBAL INDEXES를 붙여야 한다(빠뜨리면 UNUSABLE이 되고 유니크라서 적재가 ORA-01502로 멈춘다). 칸을 옮긴 행은 지우고 새로 넣는 것과 같아서 ROWID가 바뀌고 redo가 더 든다. ROWID를 저장해 두고 다시 쓰는 프로그램이 있다면 함께 확인한다. 인덱스가 하나 늘어난 만큼 적재도 조금 무거워진다.

해설

파티션 키를 조건에 더하는 처방이 틀리는 경우: 조인이나 맞추기 조건에 파티션 키를 더하면 칸을 고를 수 있어 빨라진다. 그러나 그 키가 바뀔 수 있는 값이면 결과가 달라진다. 더하기 전에 "이 키는 같은 행에서 절대 바뀌지 않는가"를 확인한다.
유니크를 LOCAL로 만들려고 파티션 키를 붙이면 유일성의 뜻이 바뀐다. 업무 키가 전 기간에서 유일해야 한다면 GLOBAL 유니크 인덱스가 지켜야 한다(교재 14화 §8).
다른 답: ON에 ORD_YM을 둔 채 MERGE 전에 '같은 주문번호가 다른 달에 있는 행'을 먼저 찾아 지우는 단계를 둘 수도 있다. 문장이 둘이 되고, 그 단계가 빠지면 같은 중복이 다시 생긴다.
시계 보정을 '월은 바꾸지 않는다'는 업무 규칙으로 막을 수도 있다. 그때도 규칙을 DB가 지키게 하려면 주문번호 유일성은 GLOBAL 인덱스로 보장해 두는 편이 안전하다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 교재 14화 규모 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 교재 14화 규모 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황