SQLP 튜닝 연습장

테마

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

스칼라 서브쿼리를 조인으로 바꿨더니 발주한 적 없는 상품 41개가 발주 1건으로 나온다

중급SELECT유통249 / 700

상황 ​

구매팀의 「상품별 월 발주 현황」은 상품 109개 전부에 대해 그달 발주 줄 수와 발주 수량을 보여 준다. 원래는 상품마다 (SELECT COUNT(*) FROM CF12 h, CF13 d WHERE …)와 (SELECT NVL(SUM(d.ORD_QTY), 0) …) 두 스칼라 서브쿼리로 구했고 14,388블록을 읽었다. 지난달 조민우가 "스칼라 서브쿼리는 행마다 돈다"며 조인으로 바꾸자 72블록이 됐다. 그런데 구매팀 오과장이 "매장에서 직접 만드는 상품은 발주를 한 적이 없는데 6월에 발주가 1건씩 찍혀 있다"라고 알려 왔다.

테이블과 인덱스 ​

  • CF01 상품: 109행 · 5블록 · CF01_PK (ITM_NO) · 그중 41개는 매장에서 만드는 상품이라 발주한 적이 없다
  • CF12 발주: 734행 · 10블록 · ORD_DT VARCHAR2(8) · 2023년 6월 28건
  • CF13 발주상품: 7,102행 · 57블록 · CF13_PK (ORD_NO, ITM_NO) · 2023년 6월 271줄(상품 68개 · 수량 24,992)
  • 통계 최신

원본 SQL ​

sql
SELECT i.ITM_NO, i.ITM_NM,
       COUNT(*)               AS ord_line_cnt,
       NVL(SUM(x.ORD_QTY), 0) AS ord_qty
  FROM CF01 i,
       (SELECT d.ITM_NO, d.ORD_QTY
          FROM CF12 h, CF13 d
         WHERE h.ORD_DT BETWEEN '20230601' AND '20230630'
           AND d.ORD_NO = h.ORD_NO) x
 WHERE x.ITM_NO(+) = i.ITM_NO
 GROUP BY i.ITM_NO, i.ITM_NM
 ORDER BY i.ITM_NO

실행계획 학습용 예시 · 건수는 적재 데이터 그대로 ​

xplan
-------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |      1 |        |    109 |00:00:00.01 |      72 |          |
|   1 |  SORT GROUP BY         |      |      1 |    109 |    109 |00:00:00.01 |      72 | 18432 (0)|
|*  2 |   HASH JOIN RIGHT OUTER|      |      1 |    312 |    312 |00:00:00.01 |      72 | 1265K (0)|
|   3 |    VIEW                |      |      1 |    271 |    271 |00:00:00.01 |      67 |          |
|*  4 |     HASH JOIN          |      |      1 |    271 |    271 |00:00:00.01 |      67 | 1237K (0)|
|*  5 |      TABLE ACCESS FULL | CF12 |      1 |     28 |     28 |00:00:00.01 |      10 |          |
|   6 |      TABLE ACCESS FULL | CF13 |      1 |   7102 |   7102 |00:00:00.01 |      57 |          |
|   7 |    TABLE ACCESS FULL   | CF01 |      1 |    109 |    109 |00:00:00.01 |       5 |          |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("X"."ITM_NO"(+)="I"."ITM_NO")
   4 - access("D"."ORD_NO"="H"."ORD_NO")
   5 - filter(("H"."ORD_DT">='20230601' AND "H"."ORD_DT"<='20230630'))

문제 ​

  1. 발주한 적 없는 상품의 발주 줄 수가 1로 나오는 이유를 실행계획의 A-Rows와 함께 설명하시오.
  2. 일량은 지금처럼 두고, 원래 스칼라 서브쿼리와 같은 답이 나오도록 SQL을 고치시오.
  3. 고친 SQL이 원래 SQL과 같은 답을 낸다는 것을 무엇으로 보일지 쓰시오.
원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.
무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자