SQLP 튜닝 연습장

테마

튜닝 연습파티션
PART-009

열린 두 달만 다시 집계하는 MERGE가 3년 치 주문을 다 읽는다

중급MERGE한빛유통384 / 700

상황 ​

월 마감 배치의 「미마감 월 재집계」는 매일 밤 마감 관리 테이블에서 아직 열려 있는(OPEN) 달을 골라 월 집계 테이블에 다시 쌓는다. 지연 정산 때문에 지금 열린 달은 2024년 5월과 6월 두 개다. 배치 담당 오과장: "두 달 치만 하는데 3년 치를 다 읽는 것 같다."

테이블과 인덱스 ​

  • T_ORD_BIG_P14 주문(파티션 복사본, 교재 14화): 1,000,000행 · PARTITION BY RANGE (ORD_DT) 월별 36칸과 빈 PMAX, 모두 37칸. P202405는 29번째, P202406은 30번째 칸이다. 칸 하나는 FULL로 약 161 ~ 166블록이고 37칸 전체는 5,868블록이다. ORD_DT는 전부 자정이다.
  • T_CLS_MON 마감 관리(문제 설정): 36행 · 4블록 · CLS_MON DATE(그 달의 첫날, PK) · CLS_ST(OPEN 2행, CLOSED 34행)
  • T_MON_SUM 월 집계(문제 설정): 36행 · 4블록 · SUM_MON DATE(PK) · ORD_CNT · ORD_AMT
  • 2024년 5월과 6월 주문은 합쳐 55,693건이다. 인덱스는 두 작은 테이블의 PK뿐이다. 통계는 최신이다.

원본 SQL ​

sql
MERGE INTO T_MON_SUM t
USING (SELECT m.CLS_MON, COUNT(*) AS ORD_CNT, SUM(o.ORD_AMT) AS ORD_AMT
         FROM T_CLS_MON m, T_ORD_BIG_P14 o
        WHERE m.CLS_ST = 'OPEN'
          AND TRUNC(o.ORD_DT, 'MM') = m.CLS_MON
        GROUP BY m.CLS_MON) s
   ON (t.SUM_MON = s.CLS_MON)
 WHEN MATCHED THEN
      UPDATE SET t.ORD_CNT = s.ORD_CNT, t.ORD_AMT = s.ORD_AMT
 WHEN NOT MATCHED THEN
      INSERT (SUM_MON, ORD_CNT, ORD_AMT) VALUES (s.CLS_MON, s.ORD_CNT, s.ORD_AMT)

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

xplan
------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name          | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
------------------------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT            |               |      1 |        |      0 | 00:00:00.31 |    5884 |        |       |
|   1 |  MERGE                     | T_MON_SUM     |      1 |        |      0 | 00:00:00.31 |    5884 |        |       |
|   2 |   VIEW                     |               |      1 |        |      2 | 00:00:00.31 |    5876 |        |       |
|*  3 |    HASH JOIN OUTER         |               |      1 |      2 |      2 | 00:00:00.31 |    5876 |        |       |
|   4 |     VIEW                   |               |      1 |      2 |      2 | 00:00:00.31 |    5872 |        |       |
|   5 |      HASH GROUP BY         |               |      1 |      2 |      2 | 00:00:00.31 |    5872 |        |       |
|*  6 |       HASH JOIN            |               |      1 |  55556 |  55693 | 00:00:00.28 |    5872 |        |       |
|*  7 |        TABLE ACCESS FULL   | T_CLS_MON     |      1 |      2 |      2 | 00:00:00.01 |       4 |        |       |
|   8 |        PARTITION RANGE ALL |               |      1 |  1000K |  1000K | 00:00:00.11 |    5868 |      1 |    37 |
|   9 |         TABLE ACCESS FULL  | T_ORD_BIG_P14 |     36 |  1000K |  1000K | 00:00:00.08 |    5868 |      1 |    37 |
|  10 |     TABLE ACCESS FULL      | T_MON_SUM     |      1 |     36 |     36 | 00:00:00.01 |       4 |        |       |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("T"."SUM_MON"(+)="S"."CLS_MON")
   6 - access("M"."CLS_MON"=TRUNC(INTERNAL_FUNCTION("O"."ORD_DT"),'fmmm'))
   7 - filter("M"."CLS_ST"='OPEN')

문제 ​

  1. T_ORD_BIG_P14를 몇 칸 열었는지 쓰고, 열린 달이 두 개뿐인데도 칸을 고르지 못한 까닭을 조인 조건으로 설명하시오.
  2. 결과가 바뀌지 않게 MERGE를 고쳐 열린 달의 칸만 읽게 하고, 그때의 조인 방식과 Pstart, Pstop, Starts를 쓰시오.
원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.
무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자