JOIN-053

지난번 단가 소급 MERGE를 기준일만 바꿔 돌렸더니 4월 판매 1만 6천 줄이 5월 단가로 덮였다

중급MERGE유통203 / 700

상황

본사 레거시 유통은 2023년 2분기에 판매가를 두 번 바꿨다. 4월 1일에 상품 30개, 5월 15일에 상품 22개다(12개는 두 번 다 바뀌었다). POS 몇 대가 2분기 내내 옛 단가로 판매를 보내서, 판매상품(CF11)의 판매단가(SLE_UPRC)를 판매일 당시 판매가로 소급해야 한다. 조민우는 지난번 6월 소급 때 쓴 MERGE를 복사해 기간과 기준일('20230515')만 바꿔 돌리고 커밋했다. 다음 날 매장 정산 담당이 "4월에 판 아메리카노가 5월 가격으로 찍혀 있다"라고 알려 왔다. 대조해 보니 고친 23,517행 가운데 16,384행은 맞던 단가를 5월 15일 단가로 덮은 것이었다.

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · CF10_PK (SLE_NO) · IX_CF10_SLE_DT (SLE_DT) · 2023년 2분기 판매 62,412건
CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO) 높이 3 · SLE_NO는 'YYYYMMDD-NNNN' 모양이고 앞 8자리가 판매일자다(판매 헤더의 SLE_DT와 다른 행 0건) · 2분기 137,214줄(CF11_PK 리프 약 543블록)
CF05 상품가격 (점이력): 157행 · 5블록 · CF05_PK (ITM_NO, UPRC_KN_CD, APL_DT) · 판매가(UPRC_KN_CD = '10') 141행 = '20220101' 89행 + '20230401' 30행 + '20230515' 22행 · 나머지는 발주단가
통계 최신

원본 SQL

sql

-- 6월 소급 때의 MERGE 에서 기간과 기준일만 바꿨다
MERGE INTO CF11 d
USING (SELECT i.ROWID AS rid, p.ITM_UPRC
         FROM CF10 h
         JOIN CF11 i ON i.SLE_NO = h.SLE_NO
         JOIN (SELECT ITM_NO,
                      MAX(ITM_UPRC) KEEP (DENSE_RANK LAST ORDER BY APL_DT) AS ITM_UPRC
                 FROM CF05
                WHERE UPRC_KN_CD = '10'
                  AND APL_DT <= '20230515'
                GROUP BY ITM_NO) p
           ON p.ITM_NO = i.ITM_NO
        WHERE h.SLE_DT BETWEEN '20230401' AND '20230630') s
   ON (d.ROWID = s.rid)
 WHEN MATCHED THEN UPDATE
      SET d.SLE_UPRC = s.ITM_UPRC
    WHERE DECODE(d.SLE_UPRC, s.ITM_UPRC, 0, 1) = 1

실행계획 학습용 예시

xplan

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT                        |                |      1 |        |      0 |00:00:01.38 |     349K|
|   1 |  MERGE                                 | CF11           |      1 |        |      0 |00:00:01.38 |     349K|
|   2 |   VIEW                                 |                |      1 |        |    137K|00:00:00.71 |     325K|
|   3 |    NESTED LOOPS                        |                |      1 |    137K|    137K|00:00:00.66 |     325K|
|*  4 |     HASH JOIN                          |                |      1 |    137K|    137K|00:00:00.39 |     187K|
|   5 |      VIEW                              |                |      1 |     89 |     89 |00:00:00.01 |       5 |
|   6 |       SORT GROUP BY                    |                |      1 |     89 |     89 |00:00:00.01 |       5 |
|*  7 |        TABLE ACCESS FULL               | CF05           |      1 |    141 |    141 |00:00:00.01 |       5 |
|   8 |      NESTED LOOPS                      |                |      1 |    137K|    137K|00:00:00.31 |     187K|
|   9 |       TABLE ACCESS BY INDEX ROWID BATCHED| CF10         |      1 |  62400 |  62412 |00:00:00.03 |     747 |
|* 10 |        INDEX RANGE SCAN                | IX_CF10_SLE_DT |      1 |  62400 |  62412 |00:00:00.01 |     175 |
|* 11 |       INDEX RANGE SCAN                 | CF11_PK        |  62412 |      2 |    137K|00:00:00.22 |     187K|
|  12 |     TABLE ACCESS BY USER ROWID         | CF11           |    137K|      1 |    137K|00:00:00.21 |     137K|
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("P"."ITM_NO"="I"."ITM_NO")
   7 - filter(("UPRC_KN_CD"='10' AND "APL_DT"<='20230515'))
  10 - access("H"."SLE_DT">='20230401' AND "H"."SLE_DT"<='20230630')
  11 - access("I"."SLE_NO"="H"."SLE_NO")

  갱신된 행 23,517 (그중 16,384행: 4월 1일 ~ 5월 14일 판매인데 5월 15일 단가로 덮였다)

문제

6월 소급 때는 맞던 MERGE가 이번에는 틀린 까닭을 CF05를 상품마다 한 행으로 줄인 방식으로 설명하시오.
판매일마다 그날 적용되던 판매가로 맞추는 MERGE를 작성하시오. 이미 잘못 덮인 16,384행은 어떻게 되는지도 쓰시오.
원천을 만드는 데 판매 헤더(CF10)가 꼭 필요한지 판단하고, 고친 MERGE의 일량을 숫자로 견주시오.

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

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

모범답안

모범 답안 SQL

sql

-- 판매일마다 그날 유효한 판매가: CF05 를 구간(시작일 이상, 다음 적용일 미만)으로 바꿔 판매일로 맞춘다
-- 판매 헤더는 필요 없다: CF11_PK 에 SLE_NO(앞 8자리 = 판매일)와 ITM_NO 가 다 있다
MERGE INTO CF11 d
USING (SELECT i.ROWID AS rid, p.ITM_UPRC
         FROM CF11 i,
              (SELECT ITM_NO, ITM_UPRC,
                      APL_DT AS FR_DT,
                      LEAD(APL_DT, 1, '99991231') OVER (PARTITION BY ITM_NO ORDER BY APL_DT) AS TO_DT
                 FROM CF05
                WHERE UPRC_KN_CD = '10') p
        WHERE i.SLE_NO >= '20230401'
          AND i.SLE_NO <  '20230701'
          AND p.ITM_NO = i.ITM_NO
          AND SUBSTR(i.SLE_NO, 1, 8) >= p.FR_DT
          AND SUBSTR(i.SLE_NO, 1, 8) <  p.TO_DT) s
   ON (d.ROWID = s.rid)
 WHEN MATCHED THEN UPDATE
      SET d.SLE_UPRC = s.ITM_UPRC
    WHERE DECODE(d.SLE_UPRC, s.ITM_UPRC, 0, 1) = 1;   -- 단가가 다른 행만 (잘못 덮인 행도 여기 걸린다)

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

xplan

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT                        |                |      1 |        |      0 |00:00:00.52 |     158K|
|   1 |  MERGE                                 | CF11           |      1 |        |      0 |00:00:00.52 |     158K|
|   2 |   VIEW                                 |                |      1 |        |    137K|00:00:00.33 |     137K|
|   3 |    NESTED LOOPS                        |                |      1 |   6860 |    137K|00:00:00.30 |     137K|
|*  4 |     HASH JOIN                          |                |      1 |   6860 |    137K|00:00:00.09 |     548 |
|   5 |      VIEW                              |                |      1 |    141 |    141 |00:00:00.01 |       5 |
|   6 |       WINDOW SORT                      |                |      1 |    141 |    141 |00:00:00.01 |       5 |
|*  7 |        TABLE ACCESS FULL               | CF05           |      1 |    141 |    141 |00:00:00.01 |       5 |
|*  8 |      INDEX RANGE SCAN                  | CF11_PK        |      1 |    137K|    137K|00:00:00.02 |     543 |
|   9 |     TABLE ACCESS BY USER ROWID         | CF11           |    137K|      1 |    137K|00:00:00.18 |     137K|
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("P"."ITM_NO"="I"."ITM_NO")
       filter((SUBSTR("I"."SLE_NO",1,8)>="P"."FR_DT" AND SUBSTR("I"."SLE_NO",1,8)<"P"."TO_DT"))
   7 - filter("UPRC_KN_CD"='10')
   8 - access("I"."SLE_NO">='20230401' AND "I"."SLE_NO"<'20230701')

  갱신된 행 16,384 (지난번에 잘못 덮인 행이 판매일 당시 단가로 돌아갔다) · 다시 돌리면 0행

모범 답안 서술

지난 MERGE는 CF05를 APL_DT <= '20230515'인 이력 가운데 상품마다 가장 늦은 한 건으로 줄였다(5번 ~ 7번 줄, 89행). 상품마다 단가가 하나뿐이니 2분기 판매 전체에 같은 단가가 붙는다. 6월 소급은 기간 안에 가격이 바뀌지 않아서 이 방식이 맞았다. 이번에는 기간 안에서 4월 1일과 5월 15일에 두 번 바뀌었으므로, 5월 15일에 바뀐 상품 22개의 4월 1일 ~ 5월 14일 판매가 아직 적용되지 않은 5월 15일 단가로 덮였다. DECODE 조건 때문에 단가가 다른 행만 고쳤는데, 맞던 행이 오히려 '다른 행'이 되어 16,384행이 틀렸다. 점이력에서 시점은 판매일이다. 판매 한 줄마다 그날 유효한 이력을 골라야 한다. LEAD(APL_DT, 1, '99991231')로 같은 상품의 다음 적용일을 붙여 판매가 141행을 '시작일 이상, 다음 적용일 미만' 구간으로 만들고, 판매일(SLE_NO 앞 8자리)이 드는 구간의 단가를 원천으로 쓴다. 판매 한 줄에 구간 하나만 맞으므로 MERGE의 원천도 대상 한 행에 한 행이다. 고친 MERGE를 다시 돌리면 잘못 덮인 16,384행은 지금 단가와 판매일 당시 단가가 다르므로 DECODE 조건에 걸려 당시 단가로 돌아간다. 처음부터 옳게 고쳐졌던 행은 이미 같은 값이라 건드리지 않는다. 한 번 더 돌리면 0행이 갱신되는 것으로 결과가 맞았는지 확인한다. 원천에는 판매 헤더가 필요 없다. 원천에 필요한 칸은 ROWID, 상품, 판매일뿐인데 셋 다 CF11_PK에 있다. CF10을 빼고 CF11_PK를 2분기 범위로 한 번 읽으면(543블록) 판매 62,412건마다 CF11_PK를 찾아가던 11번 줄(187K)이 사라진다. Buffers는 349K → 158K로 준다. 남은 137K는 대상 행을 ROWID로 찾아가 비교하는 몫이다.

해설

정해진 시점과 미지의 시점: '기준일 하나'로 줄이는 방법은 기간 안에 이력이 바뀌지 않을 때만 맞다. 기간 안에 한 번이라도 바뀌면 판매일이 시점인 이력 조인으로 써야 한다. 6월 소급 MERGE의 주석에 그 전제를 적어 두었다면 복사할 때 걸렸을 것이다.
다른 정답: 판매상품과 APL_DT <= SUBSTR(SLE_NO, 1, 8)인 가격 행을 조인한 뒤 판매 줄마다 KEEP (DENSE_RANK LAST ORDER BY APL_DT)로 고르는 원천도 맞다. 다만 13만 7천 줄로 다시 묶어야 하므로 141행을 구간으로 만드는 쪽이 가볍다. 판매 줄마다 MAX(APL_DT) 서브쿼리를 쓰는 원천은 13만 7천 번 CF05를 찾아간다.
잘못 커밋한 뒤: 되돌릴 값(판매일 당시 단가)을 SQL로 다시 계산할 수 있어서 같은 MERGE로 바로잡을 수 있었다. 계산할 수 없는 값을 덮었다면 Flashback Query(AS OF TIMESTAMP)나 백업이 필요하다. 소급 UPDATE 전에는 대상 행의 옛 값을 작업 테이블에 남겨 두는 습관이 싸다.
SLE_NO 앞 8자리와 SLE_DT가 같다는 사실은 지금 데이터에서 확인한 것이다. 이 전제를 쓰는 SQL이 늘면 CHECK 제약으로 지키게 한다 (교재 03화).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황