SQLP 튜닝 연습장

테마

튜닝 연습서브쿼리와 쿼리 변환
SUBQ-054

고객 월 매출표가 12월을 고르면 고객 평생 매출을 찍고, 6월을 고르면 한 명도 안 나온다

중급SELECT한빛유통289 / 700

상황 ​

영업팀의 「고객 월 매출표」는 연월을 고르면 그달 고객별 주문 건수와 금액을 금액순으로 보여 준다. 공통 뷰 V_CUS_ORD_SUM에 화면이 기간 조건을 건다. 재무팀이 반려했다. 2024년 12월을 고르면 고객마다 주문이 500건 안팎이고 금액 합계가 회사 3년 매출과 같다. 6월을 고르면 아무도 나오지 않는다. 개발자는 "뷰에는 바인드를 넣을 수 없어서 기간 조건을 바깥에서 걸었다. 뷰에 ORD_DT 컬럼이 있으니 같은 뜻 아니냐"라고 한다.

테이블과 인덱스 ​

  • T_ORD_BIG: 1,000,000행 · 5,826블록 · ORD_DT 2022-01-01 ~ 2024-12-31 · 고객 2,000명이 약 500건씩 · 한 달 약 27,390건(모든 고객이 매달 주문한다)
  • M_CUS: 2,000행 · 30블록 · PK_M_CUS (CUS_ID)
  • 인덱스 IX_ORD_BIG_DT_AMT (ORD_DT, ORD_AMT)가 있지만 CUS_ID는 없다 · 통계 최신
  • 아래 실행계획은 :d1 = 2024-12-01, :d2 = 2025-01-01

원본 SQL ​

sql
-- 공통 뷰
CREATE OR REPLACE VIEW V_CUS_ORD_SUM AS
SELECT CUS_ID,
       MAX(ORD_DT)  AS ORD_DT,
       COUNT(*)     AS ORD_CNT,
       SUM(ORD_AMT) AS ORD_AMT
  FROM T_ORD_BIG
 GROUP BY CUS_ID;

-- 고객 월 매출표
SELECT c.CUS_NM, v.ORD_CNT, v.ORD_AMT
  FROM V_CUS_ORD_SUM v, M_CUS c
 WHERE v.ORD_DT >= :d1
   AND v.ORD_DT <  :d2
   AND c.CUS_ID = v.CUS_ID
 ORDER BY v.ORD_AMT DESC;

실행계획 학습용 예시 ​

xplan
------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |      1 |        |   2000 |00:00:00.31 |    5856 |          |
|   1 |  SORT ORDER BY        |               |      1 |      5 |   2000 |00:00:00.31 |    5856 |  178K (0)|
|*  2 |   HASH JOIN           |               |      1 |      5 |   2000 |00:00:00.31 |    5856 | 1302K (0)|
|   3 |    VIEW               | V_CUS_ORD_SUM |      1 |      5 |   2000 |00:00:00.30 |    5826 |          |
|*  4 |     FILTER            |               |      1 |        |   2000 |00:00:00.30 |    5826 |          |
|   5 |      HASH GROUP BY    |               |      1 |      5 |   2000 |00:00:00.30 |    5826 | 1189K (0)|
|   6 |       TABLE ACCESS FULL| T_ORD_BIG    |      1 |   1000K|   1000K|00:00:00.05 |    5826 |          |
|   7 |    TABLE ACCESS FULL  | M_CUS         |      1 |   2000 |   2000 |00:00:00.01 |      30 |          |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C"."CUS_ID"="V"."CUS_ID")
   4 - filter((MAX("ORD_DT")>=:D1 AND MAX("ORD_DT")<:D2))

문제 ​

  1. 4번 줄의 filter가 무엇을 거르는지 설명하고, 12월에는 평생 매출이 나오고 6월에는 아무도 안 나오는 이유를 설명하시오.
  2. 고른 달의 주문만 묶도록 SQL을 고치시오. 공통 뷰를 계속 쓰고 싶다면 뷰를 어떻게 정의해야 바깥 기간 조건이 뷰 안으로 들어가는지도 쓰시오.
원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.
무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자