SQLP 튜닝 연습장

테마

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

「어제 세트 메뉴를 못 판 매장」이 날마다 0곳이고, 이틀 문을 닫은 매장도 빠졌다

기초SELECT버거275 / 700

상황 ​

본사 영업팀은 매일 아침 「어제 세트 메뉴를 하나도 못 판 매장」 목록을 매장 담당자에게 보낸다. 세트 판매가 0인 매장은 메뉴판이나 POS 버튼에 문제가 있는 경우가 많아서다. 지난달 이 SQL을 정리한 뒤로 목록이 날마다 0곳이다. 그런데 리모델링으로 12월 29일과 30일 이틀 문을 닫은 SH0042 매장도 그 이틀 목록에 없었다.

테이블과 인덱스 ​

  • SHOP 매장: 200행 · 5블록 · PK (SHOP_NO)
  • ORD 주문: 1,000,000행 · 22,422블록 · ORD_PK (ORD_NO) · ORD_X01 (ORD_DT, ORD_HMS) · 2013-12-30 주문 1,371건 · SHOP_NO는 NULL 허용(값은 다 있다)
  • ORD_ITEM 주문상품: 2,472,717행 · 55,265블록 · ORD_ITEM_PK (ORD_NO, ITEM_ID) · 그날 3,389줄
  • ITEM 메뉴: 92행 · 5블록 · ITEM_PK (ITEM_ID) · 세트 메뉴(ITEM_TYPE_CD = '100101') 17개
  • 2013-12-30에 세트를 판 매장은 191곳이다 · 통계 최신 · :v_dt = '20131230'

원본 SQL ​

sql
SELECT s.SHOP_NO, s.SHOP_NM
  FROM SHOP s
 WHERE NOT EXISTS (SELECT 1
                     FROM ORD o, ORD_ITEM d, ITEM i
                    WHERE o.SHOP_NO = SHOP_NO
                      AND o.ORD_DT  = :v_dt
                      AND d.ORD_NO  = o.ORD_NO
                      AND i.ITEM_ID = d.ITEM_ID
                      AND i.ITEM_TYPE_CD = '100101')
 ORDER BY s.SHOP_NO

실행계획 학습용 예시 ​

xplan
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |             |      1 |        |      0 |00:00:00.01 |      28 |
|   1 |  SORT ORDER BY                          |             |      1 |    199 |      0 |00:00:00.01 |      28 |
|*  2 |   FILTER                                |             |      1 |        |      0 |00:00:00.01 |      28 |
|   3 |    TABLE ACCESS FULL                    | SHOP        |      0 |    200 |      0 |00:00:00.01 |       0 |
|   4 |    NESTED LOOPS                         |             |      1 |      1 |      1 |00:00:00.01 |      28 |
|   5 |     NESTED LOOPS                        |             |      1 |      2 |      7 |00:00:00.01 |      14 |
|*  6 |      TABLE ACCESS BY INDEX ROWID BATCHED| ORD         |      1 |   1371 |      3 |00:00:00.01 |       5 |
|*  7 |       INDEX RANGE SCAN                  | ORD_X01     |      1 |   1371 |      3 |00:00:00.01 |       3 |
|*  8 |      INDEX RANGE SCAN                   | ORD_ITEM_PK |      3 |      2 |      7 |00:00:00.01 |       9 |
|*  9 |     TABLE ACCESS BY INDEX ROWID         | ITEM        |      7 |      1 |      1 |00:00:00.01 |      14 |
|* 10 |      INDEX UNIQUE SCAN                  | ITEM_PK     |      7 |      1 |      7 |00:00:00.01 |       7 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter( NOT EXISTS (SELECT 0 FROM "ITEM" "I","ORD_ITEM" "D","ORD" "O" WHERE "O"."ORD_DT"=:V_DT AND
              "O"."SHOP_NO" IS NOT NULL AND "D"."ORD_NO"="O"."ORD_NO" AND "I"."ITEM_ID"="D"."ITEM_ID" AND
              "I"."ITEM_TYPE_CD"='100101'))
   6 - filter("O"."SHOP_NO" IS NOT NULL)
   7 - access("O"."ORD_DT"=:V_DT)
   8 - access("D"."ORD_NO"="O"."ORD_NO")
   9 - filter("I"."ITEM_TYPE_CD"='100101')
  10 - access("I"."ITEM_ID"="D"."ITEM_ID")

문제 ​

  1. 목록이 날마다 0곳인 까닭을 서브쿼리의 상관 조건으로 설명하시오. 실행계획 2번 줄의 조건과 3번, 4번 줄의 Starts가 무엇을 뜻하는지도 쓰시오.
  2. 매장마다 세트 판매가 있는지 판정하도록 SQL을 고치시오. 같은 실수를 막는 작성 규칙도 쓰시오.
원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.
무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자