SQLP 튜닝 연습장

테마

튜닝 연습소트 튜닝과 페이징
SORT-046

CUBE로 바꾼 교차표에서 「미결제」 줄이 사라지고 전체 합계 칸이 열 때마다 바뀐다

중급SELECT한빛유통351 / 700

상황 ​

정산팀의 「결제수단 × 주문상태」 화면은 기간(기본 최근 1년)의 주문을 결제수단과 주문상태로 나눈 교차표다. 칸마다 건수와 금액을 보여 주고, 오른쪽 끝에는 결제수단별 합계, 맨 아래에는 상태별 합계와 전체 합계가 붙는다. 화면은 받은 줄을 결제수단 이름과 상태 이름으로 찾아 칸에 넣는다. 예전 SQL은 칸, 결제수단별 합계, 상태별 합계, 전체 합계를 네 번 따로 세어 UNION ALL로 이었고 T_ORD를 네 번 읽었다(3,584블록). 지난주 조민우가 CUBE로 한 번에 세도록 바꿨는데, 그 뒤로 표에서 「미결제」 줄이 사라지고 전체 합계 칸이 4,609와 50,112 사이를 오간다는 문의가 들어왔다.

테이블과 인덱스 ​

  • T_ORD 주문: 100,000행 · 896블록 · PAY_TP 결제수단(CARD · BANK · PHONE · POINT, 결제 전에는 NULL) · ORD_ST 주문상태(COMPLETE · PENDING · CANCEL, NOT NULL) · ORD_AMT
  • 2024년 주문은 50,112건이다. COMPLETE 45,101건은 모두 결제됐고, PENDING 4,009건은 모두 결제 전이다. CANCEL 1,002건 가운데 402건은 결제 뒤에, 600건은 결제 전에 취소됐다. 그래서 PAY_TP가 NULL인 주문이 4,609건이다.
  • 기간 조건에 쓸 인덱스는 없다(1년이면 테이블의 절반이라 FULL이 싸다). 통계는 최신이다.

원본 SQL ​

sql
SELECT NVL(PAY_TP, '합계') AS pay_nm,
       NVL(ORD_ST, '합계') AS st_nm,
       COUNT(*)            AS ord_cnt,
       SUM(ORD_AMT)        AS ord_amt
  FROM T_ORD
 WHERE ORD_DT >= :from_dt AND ORD_DT < :to_dt
 GROUP BY CUBE (PAY_TP, ORD_ST)
 ORDER BY 1, 2

실행계획 학습용 예시 ​

xplan
[2024년]
------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |       |      1 |        |     19 |00:00:00.03 |     896 |          |
|   1 |  SORT ORDER BY         |       |      1 |     24 |     19 |00:00:00.03 |     896 | 2048 (0) |
|   2 |   SORT GROUP BY        |       |      1 |     24 |     19 |00:00:00.03 |     896 | 2048 (0) |
|   3 |    GENERATE CUBE       |       |      1 |     24 |     40 |00:00:00.03 |     896 |          |
|   4 |     SORT GROUP BY      |       |      1 |     24 |     10 |00:00:00.03 |     896 | 2048 (0) |
|*  5 |      FILTER            |       |      1 |        |  50112 |00:00:00.02 |     896 |          |
|*  6 |       TABLE ACCESS FULL| T_ORD |      1 |  50000 |  50112 |00:00:00.01 |     896 |          |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - filter(:FROM_DT<:TO_DT)
   6 - filter(("ORD_DT">=:FROM_DT AND "ORD_DT"<:TO_DT))

[결과 가운데 결제수단 이름표가 '합계'인 줄 · 받은 순서]
  PAY_NM  ST_NM      ORD_CNT
  합계     CANCEL         600
  합계     CANCEL       1,002
  합계     COMPLETE    45,101
  합계     PENDING      4,009
  합계     PENDING      4,009
  합계     합계         4,609
  합계     합계        50,112

문제 ​

  1. 「미결제」 줄이 사라지고 이름표가 같은 줄이 둘씩 나오는 까닭을 데이터와 CUBE가 만드는 묶음으로 설명하시오.
  2. T_ORD를 한 번만 읽는 모양은 지키면서 이름표가 바르게 나오도록 SQL을 고치시오. 화면이 칸을 찾을 때 쓸 묶음 번호도 함께 내시오.
원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.
무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자