SQLP 튜닝 연습장

테마

튜닝 연습조인 튜닝
JOIN-038

매장별 상품 판매표에 (+)를 마저 붙였더니 여섯 매장이 다 나오는데 영수증 수가 그날 전체 영수증 수로 찍힌다

중급SELECT유통188 / 700

상황 ​

상품팀의 「매장별 상품 판매」는 상품 하나와 날짜를 고르면 매장 6곳마다 그 상품이 들어간 영수증 수와 판매 수량을 보여 준다(안 팔린 매장은 0). 처음 SQL은 4곳만 나와서 반려됐고, 조민우가 "아우터 조인 조건에 (+)를 빠뜨렸다"라며 판매상품 쪽 조건에도 (+)를 붙였다. 이번에는 6곳이 다 나오고 수량도 맞는데, 영수증 수 칸이 그 매장의 그날 전체 영수증 수와 똑같다는 문의가 왔다. 아래는 2023-12-24, 상품 ITM033으로 조회한 실행계획이다.

테이블과 인덱스 ​

  • CF07 매장: 6행 · 4블록 · PK (SHP_NO)
  • CF10 판매: 500,000행 · 4,583블록 · CF10_PK (SLE_NO) · IX_CF10_SLE_DT (SLE_DT) (03화에서 만들었다) · 하루 약 700건
  • CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO) · 영수증 한 장에 평균 2.2줄
  • 2023-12-24: 영수증 706장(6개 매장 모두 영업) · 그중 ITM033이 들어간 영수증 41장(4개 매장) · 그 상품이 안 팔린 매장 2곳 · 통계 최신

원본 SQL ​

sql
SELECT s.SHP_NO, s.SHP_NM,
       COUNT(a.SLE_NO)        AS rcpt_cnt,
       NVL(SUM(b.SLE_QTY), 0) AS sle_qty
  FROM CF07 s, CF10 a, CF11 b
 WHERE a.SHP_NO(+) = s.SHP_NO
   AND a.SLE_DT(+) = :v_dt
   AND b.SLE_NO(+) = a.SLE_NO
   AND b.ITM_NO(+) = :v_itm
 GROUP BY s.SHP_NO, s.SHP_NM
 ORDER BY s.SHP_NO
-- 처음 SQL : 아래 두 줄에 (+) 가 없었다 (4 줄)
--   AND b.SLE_NO = a.SLE_NO
--   AND b.ITM_NO = :v_itm

실행계획 학습용 예시 ​

xplan
------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                |      1 |        |      6 |00:00:00.01 |    1478 |
|   1 |  SORT GROUP BY                        |                |      1 |      6 |      6 |00:00:00.01 |    1478 |
|   2 |   NESTED LOOPS OUTER                  |                |      1 |    706 |    706 |00:00:00.01 |    1478 |
|*  3 |    HASH JOIN OUTER                    |                |      1 |    706 |    706 |00:00:00.01 |      19 |
|   4 |     TABLE ACCESS FULL                 | CF07           |      1 |      6 |      6 |00:00:00.01 |       4 |
|   5 |     TABLE ACCESS BY INDEX ROWID BATCHED| CF10          |      1 |    706 |    706 |00:00:00.01 |      15 |
|*  6 |      INDEX RANGE SCAN                 | IX_CF10_SLE_DT |      1 |    706 |    706 |00:00:00.01 |       5 |
|   7 |    TABLE ACCESS BY INDEX ROWID        | CF11           |    706 |      1 |     41 |00:00:00.01 |    1459 |
|*  8 |     INDEX UNIQUE SCAN                 | CF11_PK        |    706 |      1 |     41 |00:00:00.01 |    1418 |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("A"."SHP_NO"(+)="S"."SHP_NO")
   6 - access("A"."SLE_DT"(+)=:V_DT)
   8 - access("B"."SLE_NO"(+)="A"."SLE_NO" AND "B"."ITM_NO"(+)=:V_ITM)

문제 ​

  1. 처음 SQL이 4곳만 낸 이유를 쓰시오. 지금 SQL이 수량은 맞게 내면서 영수증 수만 그날 전체 영수증 수로 내는 이유를 실행계획 3번 줄과 2번 줄의 A-Rows로 설명하시오.
  2. 매장 6곳이 모두 나오고 영수증 수와 수량이 그 상품 기준으로 나오도록 SQL을 고치시오. (+) 표기만으로는 이 요구를 표현할 수 없는 이유도 쓰시오.
원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.
무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자