하루 정정 수십 건을 반영하는 정보계 판매 MERGE가 정보계 판매를 통째로 읽는다

중급MERGE유통71 / 700

상황

유통 정보계는 운영 판매(CF10)를 복제한 「정보계 판매」 테이블을 둔다. 매일 01시에 전날 판매를 넣고, 02시에 「판매 정정 반영」 MERGE가 전날 고쳐진 판매(반품 취소, 매장 정정, 늦게 등록된 지난 판매)를 반영한다. 정정은 하루 30 ~ 80건이다. 그런데 MERGE 한 번에 0.8초쯤 걸리고, 정보계 판매가 커질수록 늘어난다. 정보계 DBA는 "PK가 있는데 왜 다 읽느냐"고 묻는다. 아래는 2023-12-15 정정분(52건)을 반영할 때다.

테이블과 인덱스

CF10 판매(운영): 500,000행 · 4,583블록 · MOD_DTT DATE(고친 시각) · 인덱스 IX_CF10_MOD (MOD_DTT) (문제 설정: IDX-034와 같다. 판매를 등록하거나 고칠 때마다 MOD_DTT가 찍힌다)
DW_SLE 정보계 판매(이 문제에서 쓰는 파생 테이블, CF10의 복제): 500,000행 · 4,640블록 · SLE_NO · SLE_DT · SLE_HMS · SHP_NO · CNCL_DTT · 날짜순으로 쌓인다
PK_DW_SLE (SLE_DT, SLE_NO): 정보계 규칙대로 날짜를 맨 앞에 둔 PK · BLEVEL 2 · 리프 약 1,900블록 · 다른 인덱스는 없다
판매번호 앞 8자리는 판매일자다. 판매일자는 정정 대상이 아니다(날짜가 틀린 판매는 취소하고 새로 등록한다)
2023-12-15에 고쳐진 판매 52건: 정보계에 이미 있는 판매 49건, 늦게 등록된 지난 판매 3건

원본 SQL

sql

MERGE INTO DW_SLE t
USING (SELECT SLE_NO, SLE_DT, SLE_HMS, SHP_NO, CNCL_DTT
         FROM CF10
        WHERE MOD_DTT >= TO_DATE(:v_ymd, 'YYYYMMDD')
          AND MOD_DTT <  TO_DATE(:v_ymd, 'YYYYMMDD') + 1) s
   ON (t.SLE_NO = s.SLE_NO)
 WHEN MATCHED THEN
   UPDATE SET t.SHP_NO = s.SHP_NO, t.CNCL_DTT = s.CNCL_DTT
 WHEN NOT MATCHED THEN
   INSERT (SLE_NO, SLE_DT, SLE_HMS, SHP_NO, CNCL_DTT)
   VALUES (s.SLE_NO, s.SLE_DT, s.SLE_HMS, s.SHP_NO, s.CNCL_DTT)

실행계획 학습용 예시

xplan

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT                       |             |      1 |        |      0 |00:00:00.84 |    4767 |
|   1 |  MERGE                                | DW_SLE      |      1 |        |      0 |00:00:00.84 |    4767 |
|   2 |   VIEW                                |             |      1 |        |     52 |00:00:00.83 |    4697 |
|*  3 |    HASH JOIN OUTER                    |             |      1 |     41 |     52 |00:00:00.83 |    4697 |
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| CF10       |      1 |     41 |     52 |00:00:00.01 |      57 |
|*  5 |      INDEX RANGE SCAN                 | IX_CF10_MOD |      1 |     41 |     52 |00:00:00.01 |       4 |
|   6 |     TABLE ACCESS FULL                 | DW_SLE      |      1 |   500K |   500K |00:00:00.79 |    4640 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("T"."SLE_NO"="SLE_NO")
   5 - access("MOD_DTT">=TO_DATE(:V_YMD,'YYYYMMDD') AND "MOD_DTT"<TO_DATE(:V_YMD,'YYYYMMDD')+1)

문제

PK가 있는데도 정보계 판매를 통째로 읽는 이유를 ON 절의 조건과 PK의 선두 컬럼으로 설명하시오.
결과가 바뀌지 않게 MERGE를 고쳐 PK로 찾아가게 하고, 그렇게 고쳐도 되는 근거를 설명하시오.
정보계 판매가 계속 커질 때 고치기 전과 고친 뒤의 비용이 각각 무엇에 비례하는지 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

MERGE INTO DW_SLE t
USING (SELECT SLE_NO, SLE_DT, SLE_HMS, SHP_NO, CNCL_DTT
         FROM CF10
        WHERE MOD_DTT >= TO_DATE(:v_ymd, 'YYYYMMDD')
          AND MOD_DTT <  TO_DATE(:v_ymd, 'YYYYMMDD') + 1) s
   ON (t.SLE_DT = s.SLE_DT          -- PK 의 선두 컬럼 (판매일자는 정정 대상이 아니다)
   AND t.SLE_NO = s.SLE_NO)
 WHEN MATCHED THEN
   UPDATE SET t.SHP_NO = s.SHP_NO, t.CNCL_DTT = s.CNCL_DTT
 WHEN NOT MATCHED THEN
   INSERT (SLE_NO, SLE_DT, SLE_HMS, SHP_NO, CNCL_DTT)
   VALUES (s.SLE_NO, s.SLE_DT, s.SLE_HMS, s.SHP_NO, s.CNCL_DTT)

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

xplan

------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT                        |             |      1 |        |      0 |00:00:00.01 |     332 |
|   1 |  MERGE                                 | DW_SLE      |      1 |        |      0 |00:00:00.01 |     332 |
|   2 |   VIEW                                 |             |      1 |        |     52 |00:00:00.01 |     262 |
|   3 |    NESTED LOOPS OUTER                  |             |      1 |     41 |     52 |00:00:00.01 |     262 |
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| CF10        |      1 |     41 |     52 |00:00:00.01 |      57 |
|*  5 |      INDEX RANGE SCAN                  | IX_CF10_MOD |      1 |     41 |     52 |00:00:00.01 |       4 |
|   6 |     TABLE ACCESS BY INDEX ROWID        | DW_SLE      |     52 |      1 |     49 |00:00:00.01 |     205 |
|*  7 |      INDEX UNIQUE SCAN                 | PK_DW_SLE   |     52 |      1 |     49 |00:00:00.01 |     156 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("MOD_DTT">=TO_DATE(:V_YMD,'YYYYMMDD') AND "MOD_DTT"<TO_DATE(:V_YMD,'YYYYMMDD')+1)
   7 - access("T"."SLE_DT"="SLE_DT" AND "T"."SLE_NO"="SLE_NO")

모범 답안 서술

3번 줄은 원천 52건과 정보계 판매를 해시 조인하고, 6번 줄에서 정보계 판매 50만 행을 통째로 읽는다(4,640블록). ON 절이 대상 쪽에 넘기는 조건은 t.SLE_NO = s.SLE_NO 하나인데 PK_DW_SLE는 SLE_DT가 선두다. SLE_NO만으로는 PK의 어디부터 찾을지 정할 수 없으므로, 52건을 PK로 하나씩 찾아가는 NL 조인을 쓸 수 없다. 남는 길은 전체를 읽는 해시 조인이나 날짜 730종을 건너뛰는 SKIP SCAN을 52번 하는 것뿐이고, 옵티마이저는 해시 조인을 골랐다. 원천의 52건은 각자 자기 판매일자를 알고 있다. ON 절에 t.SLE_DT = s.SLE_DT를 더하면 PK의 두 컬럼이 모두 등치가 되어 한 건마다 INDEX UNIQUE SCAN으로 찾아간다. 판매일자는 판매번호 앞 8자리와 같고 정정 대상이 아니므로(날짜가 틀린 판매는 취소하고 새로 등록한다), 조건을 더해도 맞는 행이 바뀌지 않는다. ON 절에 쓴 컬럼은 UPDATE SET에 쓸 수 없지만(ORA-38104), 이 MERGE는 SLE_DT를 고치지 않으므로 걸리지 않는다. 결과는 7번 줄처럼 52번 찾아가 49건이 맞고, 3건은 새로 넣는다(Buffers 4,767 → 332). 고치기 전의 비용은 정보계 판매의 크기에 비례한다. 판매가 쌓여 100만 행이 되면 MERGE도 두 배를 읽는다. 고친 뒤의 비용은 정정 건수에 비례한다. 한 건에 PK 3블록과 테이블 1블록이라, 정보계 판매가 몇 배로 커져도 정정이 52건이면 300블록 남짓이다.

해설

다른 답: 정보계 판매에 (SLE_NO) 유니크 인덱스를 하나 더 만들어도 된다. 하지만 01시 일 적재가 그 인덱스도 고쳐야 하고 저장 공간이 는다. 이미 아는 값을 ON 절에 넣는 쪽은 공짜다.
정정이 수만 건인 날(재처리)에는 해시 조인이 오히려 싸다. 조건을 더해 두면 옵티마이저가 건수에 따라 NL과 해시 가운데 고를 수 있다. 조건이 없으면 고를 길이 하나뿐이다.
날짜가 선두인 PK는 정보계에서 흔하다(날짜 단위 적재, 파티션 키). 그런 테이블과 조인하거나 MERGE할 때는 날짜를 함께 주는 것을 코딩 규칙으로 둔다.
판매일자를 정정해야 하는 업무가 생기면 이 방법은 쓸 수 없다. 그때는 (SLE_NO) 유니크 인덱스를 두는 안으로 돌아간다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황