회원 동기화에 조건을 붙였더니 생년월일을 새로 입력한 고객이 DW로 넘어가지 않는다

중급MERGE한빛유통450 / 700

상황

한빛유통은 매일 01:00에 회원계(CRM)가 보낸 고객 전체 파일(STG_CUS)을 M_CUS에 맞추고, 02:00에 DW가 UPD_DT가 어제인 고객을 변경분으로 가져간다. 처음 판은 MERGE가 날마다 2,000명 전원의 UPD_DT를 고쳐서, DW 고객 이력에 하루 2,000행씩 새 버전이 쌓였다(실제로 바뀐 고객은 하루 10명 안팎이다). 그래서 개발자가 WHEN MATCHED에 <> 비교 조건을 붙였다. 변경분은 하루 8명으로 줄었는데, 이번에는 정산팀 조민우가 문의를 했다. "생년월일을 새로 입력한 고객 세 명이 DW에 안 넘어왔어요."

테이블과 인덱스

M_CUS: 2,000행 · 30블록 · PK_M_CUS (CUS_ID) · 이 문제에서는 UPD_DT DATE 칸이 더 있다(DW 변경분 추출 기준)
RGN_ID, GND_TP, BTH_YMD는 NULL을 허용한다(회원이 입력하지 않으면 NULL). CUS_NM은 NOT NULL이다.
STG_CUS: 회원계가 매일 보내는 전체 고객 파일 · 2,000행 · 30블록 · CUS_ID는 적재할 때 유일성을 검사한다
회원계 변경 로그로 센 어제의 실제 변경은 12명이다(이름 2, 지역 6, 생년월일 NULL에서 값으로 3, 성별 값에서 NULL로 1).
CUS_GD는 회원계가 아니라 한빛유통 등급 배치가 정하므로 동기화하지 않는다.

원본 SQL

sql

MERGE INTO M_CUS c
USING STG_CUS s
   ON (c.CUS_ID = s.CUS_ID)
 WHEN MATCHED THEN UPDATE
      SET c.CUS_NM  = s.CUS_NM,
          c.RGN_ID  = s.RGN_ID,
          c.GND_TP  = s.GND_TP,
          c.BTH_YMD = s.BTH_YMD,
          c.UPD_DT  = SYSDATE
    WHERE c.CUS_NM  <> s.CUS_NM          -- 지난주에 붙인 조건 (바뀐 고객만)
       OR c.RGN_ID  <> s.RGN_ID
       OR c.GND_TP  <> s.GND_TP
       OR c.BTH_YMD <> s.BTH_YMD
 WHEN NOT MATCHED THEN INSERT (CUS_ID, CUS_NM, RGN_ID, GND_TP, BTH_YMD, CUS_GD, UPD_DT)
      VALUES (s.CUS_ID, s.CUS_NM, s.RGN_ID, s.GND_TP, s.BTH_YMD, 'C', SYSDATE);

실행계획 학습용 예시

xplan

[어제 밤 같은 파일 — 판별 비교]
                    MERGE 갱신 행   DW 변경분   회원계 변경 로그
  처음 판 (조건 없음)        2,000       2,000          12
  지금 판 (<> 조건)              8           8          12   (빠진 4명: 생년월일 NULL → 값 3 · 성별 값 → NULL 1)

------------------------------------------------------------------------------------------
| Id  | Operation            | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT      |         |      1 |        |      0 |00:00:00.02 |      74 |
|   1 |  MERGE               | M_CUS   |      1 |        |      0 |00:00:00.02 |      74 |
|   2 |   VIEW               |         |      1 |        |   2000 |00:00:00.01 |      62 |
|*  3 |    HASH JOIN OUTER   |         |      1 |   2000 |   2000 |00:00:00.01 |      62 |
|   4 |     TABLE ACCESS FULL| STG_CUS |      1 |   2000 |   2000 |00:00:00.01 |      31 |
|   5 |     TABLE ACCESS FULL| M_CUS   |      1 |   2000 |   2000 |00:00:00.01 |      31 |
------------------------------------------------------------------------------------------

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

문제

처음 판이 매일 2,000명을 변경분으로 넘긴 이유와, 지금 판이 4명을 놓친 이유를 설명하시오.
실제로 바뀐 고객만 빠짐없이 고치도록 MERGE를 고치고, 결과가 맞는지 어떻게 확인할지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

MERGE INTO M_CUS c
USING STG_CUS s
   ON (c.CUS_ID = s.CUS_ID)
 WHEN MATCHED THEN UPDATE
      SET c.CUS_NM  = s.CUS_NM,
          c.RGN_ID  = s.RGN_ID,
          c.GND_TP  = s.GND_TP,
          c.BTH_YMD = s.BTH_YMD,
          c.UPD_DT  = SYSDATE
    WHERE c.CUS_NM <> s.CUS_NM                       -- NOT NULL 칸은 <> 로 충분하다
       OR DECODE(c.RGN_ID,  s.RGN_ID,  0, 1) = 1     -- NULL 허용 칸은 NULL끼리 같다고 보는 DECODE
       OR DECODE(c.GND_TP,  s.GND_TP,  0, 1) = 1
       OR DECODE(c.BTH_YMD, s.BTH_YMD, 0, 1) = 1
 WHEN NOT MATCHED THEN INSERT (CUS_ID, CUS_NM, RGN_ID, GND_TP, BTH_YMD, CUS_GD, UPD_DT)
      VALUES (s.CUS_ID, s.CUS_NM, s.RGN_ID, s.GND_TP, s.BTH_YMD, 'C', SYSDATE);

-- 확인: 고친 건수(SQL%ROWCOUNT에서 신규 가입을 뺀 수)를 회원계 변경 로그 건수와 맞춘다 (어제 12 = 12)

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

xplan

------------------------------------------------------------------------------------------
| Id  | Operation            | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT      |         |      1 |        |      0 |00:00:00.02 |      79 |
|   1 |  MERGE               | M_CUS   |      1 |        |      0 |00:00:00.02 |      79 |
|   2 |   VIEW               |         |      1 |        |   2000 |00:00:00.01 |      62 |
|*  3 |    HASH JOIN OUTER   |         |      1 |   2000 |   2000 |00:00:00.01 |      62 |
|   4 |     TABLE ACCESS FULL| STG_CUS |      1 |   2000 |   2000 |00:00:00.01 |      31 |
|   5 |     TABLE ACCESS FULL| M_CUS   |      1 |   2000 |   2000 |00:00:00.01 |      31 |
------------------------------------------------------------------------------------------

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

[고친 판 — 같은 파일] MERGE 갱신 12 · DW 변경분 12 · 회원계 변경 로그 12

모범 답안 서술

처음 판은 WHEN MATCHED에 조건이 없어서 원천과 맞는 2,000명 전원을 매일 고쳤다. UPDATE는 값이 같은지 따지지 않으므로 바뀐 칸이 없어도 UPD_DT = SYSDATE가 적히고, DW는 UPD_DT만 보고 2,000명 전원을 변경분으로 가져갔다. UPD_DT가 '값이 바뀐 시각'이 아니라 '배치가 돈 시각'이 된 것이다. 지금 판의 <> 조건은 NULL을 다루지 못한다. 한쪽이 NULL이면 c.BTH_YMD <> s.BTH_YMD는 참도 거짓도 아닌 UNKNOWN이고, WHERE는 참인 행만 고친다. 그래서 NULL에서 값으로 바뀐 고객(생년월일 3명)과 값에서 NULL로 바뀐 고객(성별 1명)이 빠져, 12명 가운데 8명만 넘어갔다. NULL을 허용하는 칸은 DECODE(c.칸, s.칸, 0, 1) = 1로 비교한다. DECODE는 NULL과 NULL을 같다고 보고, NULL과 값은 다르다고 본다. NOT NULL인 CUS_NM은 <>로 충분하다. 이렇게 하면 어제 파일 기준으로 고친 행이 2,000 → 12이고, 빠지는 고객도 없다. 일량(74 → 79블록)은 원래 작았다. 이 문제의 비용은 DW 이력과 변경분의 정확성이다. 확인은 숫자로 한다. MERGE가 고친 건수에서 신규 가입 건수를 뺀 값이 회원계 변경 로그의 건수와 같은지(12 = 12) 배치가 스스로 대조하게 한다. 변경분이 줄었다고 맞는 것은 아니다. 지금 판도 '줄었다'만 확인하고 넘어갔다.

해설

LNNVL의 함정: LNNVL(c.BTH_YMD = s.BTH_YMD)는 NULL에서 값으로 바뀐 고객을 잡지만, 양쪽이 다 NULL인 고객(생년월일을 입력하지 않은 고객 전부)도 '다르다'로 본다. 그 고객들이 매일 다시 고쳐져 변경분이 다시 늘어난다.
NVL(c.BTH_YMD, '#') <> NVL(s.BTH_YMD, '#')도 동작하지만, 대체값('#')이 실제 값으로 들어올 수 없다는 전제가 붙는다. 칸의 타입마다 대체값을 따로 골라야 한다.
비교할 칸 목록과 SET 칸 목록은 같이 관리한다. SET에 칸을 하나 더하고 WHERE에 빠뜨리면 그 칸만 바뀐 고객은 다시 놓친다.
원천이 전체 파일이 아니라 변경분 파일이라면 비교 조건 없이도 되지만, 회원계가 같은 고객을 다시 보내는 날이 있으므로 비교는 남겨 두는 편이 안전하다.
줄었다는 것과 맞다는 것은 다르다. 기준(회원계 변경 로그)과 건수를 맞춰 보는 것이 교재 08화 「정합성 회귀 체크리스트」의 A(기준은 원장)다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황