SQLP 튜닝 연습장

테마

튜닝 연습조인 튜닝
JOIN-043

"외부 조인은 해시로"라는 주석이 붙은 VIP 30일 요약 칸이 한 번에 주문 100만 건을 훑는다

중급SELECT한빛유통193 / 700

상황 ​

상담 화면 맨 위의 「VIP 최근 30일」 칸은 A 등급 고객 20명마다 최근 30일 주문 건수, 금액, 취소 건수를 보여 준다. 30일 동안 주문이 없는 VIP도 0으로 보여야 해서 아우터 조인으로 썼다. 상담 화면이 열릴 때마다 호출되어 하루 3천 번쯤 돈다. 힌트 옆에는 -- 외부 조인은 해시로 (등급별 주문 상태 리포트 튜닝 때 결론)이라는 주석이 있다. 아래는 2024-06-01 ~ 2024-06-30으로 조회한 실행계획이다.

테이블과 인덱스 ​

  • M_CUS: 2,000행 · 30블록 · PK_M_CUS (CUS_ID) · A 등급 20명
  • T_ORD_BIG: 1,000,000행 · 5,826블록 · 30일 주문 약 27,400건 · 고객 한 명은 한 달 평균 13.7건
  • 인덱스 IX_ORD_BIG_CUS_DT (CUS_ID, ORD_DT, ORD_AMT): 영업팀 화면용으로 작년에 만들었다 · ORD_ST는 담지 않는다
  • 조회 기간 VIP 주문 274건(VIP 19명) · 주문이 없는 VIP 1명 · 통계 최신

원본 SQL ​

sql
SELECT /*+ LEADING(c o) USE_HASH(o) */     -- 외부 조인은 해시로 (등급별 주문 상태 리포트 튜닝 때 결론)
       c.CUS_ID, c.CUS_NM,
       COUNT(o.ORD_SEQ)       AS ord_cnt,
       NVL(SUM(o.ORD_AMT), 0) AS ord_amt,
       COUNT(CASE WHEN o.ORD_ST = 'CANCEL' THEN 1 END) AS cancel_cnt
  FROM M_CUS c, T_ORD_BIG o
 WHERE c.CUS_GD = 'A'
   AND o.CUS_ID(+) = c.CUS_ID
   AND o.ORD_DT(+) >= :v_from
   AND o.ORD_DT(+) <  :v_to
 GROUP BY c.CUS_ID, c.CUS_NM

실행계획 학습용 예시 ​

xplan
---------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |      1 |        |     20 |00:00:00.09 |    5856 |          |
|   1 |  HASH GROUP BY      |           |      1 |     20 |     20 |00:00:00.09 |    5856 | 1045K (0)|
|*  2 |   HASH JOIN OUTER   |           |      1 |    274 |    275 |00:00:00.09 |    5856 | 1250K (0)|
|*  3 |    TABLE ACCESS FULL| M_CUS     |      1 |     20 |     20 |00:00:00.01 |      30 |          |
|*  4 |    TABLE ACCESS FULL| T_ORD_BIG |      1 |  27390 |  27403 |00:00:00.08 |    5826 |          |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("O"."CUS_ID"(+)="C"."CUS_ID")
   3 - filter("C"."CUS_GD"='A')
   4 - filter(("O"."ORD_DT">=:V_FROM AND "O"."ORD_DT"<:V_TO))

문제 ​

  1. 5,856블록이 어디서 나오는지 계획의 줄 번호로 설명하고, 이 칸에 해시 조인이 맞지 않는 이유를 행 수로 쓰시오.
  2. 개선 SQL을 작성하고, 아우터 조인에서 보존할 쪽이 드라이빙으로 고정된다는 제약이 이 SQL에서는 어떻게 작용하는지 설명하시오.
  3. 주석의 "외부 조인은 해시로"가 맞는 경우를 쓰시오.
원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.
무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자