SQLP 튜닝 연습장

테마

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

부산 매장 열 곳의 하루 판매를 보는데 그날 주문 1,368건의 상품을 다 읽는다

기초SELECT버거255 / 700

상황 ​

본사 영업팀의 「지역 매장 일 판매」 화면은 지역(주소 앞부분)과 날짜를 고르면 그 지역 매장의 상품별 판매 수량을 보여 준다. 지역 담당자 20명이 하루 수백 번 누른다. 서브쿼리의 NO_UNNEST는 외주 개발 시절의 팀 표준이고, 주석에는 "매장은 200곳뿐이라 FILTER로 두면 캐시된다"라고 적혀 있다. 팀은 표준을 지우기 전에 다른 방법이 있는지부터 알고 싶어 한다.

테이블과 인덱스 ​

  • ORD 주문: 1,000,000행 · 22,422블록 · ORD_PK (ORD_NO) · ORD_X01 (ORD_DT, ORD_HMS) (날짜순 적재, 하루치가 약 31블록에 모여 있다) · 매장 200곳의 주문이 고르게 섞여 있다
  • ORD_ITEM 주문상품: 2,472,717행 · 55,265블록 · ORD_ITEM_PK (ORD_NO, ITEM_ID) (높이 3) · 주문당 1~4줄 · 주문번호 순으로 쌓여 있다
  • SHOP 매장: 200행 · 5블록 · SHOP_PK (SHOP_NO) · 주소가 '부산'으로 시작하는 매장 10곳
  • 2013-03-15 주문 1,368건 · 주문상품 3,381줄 · 그중 부산 10곳의 주문 69건 · 170줄 · 통계 최신

원본 SQL ​

sql
SELECT i.ITEM_ID, SUM(i.ORD_ITEM_QTY) AS qty
  FROM ORD o, ORD_ITEM i
 WHERE o.ORD_DT = :dt
   AND i.ORD_NO = o.ORD_NO
   AND o.SHOP_NO IN (SELECT /*+ NO_UNNEST */ s.SHOP_NO
                       FROM SHOP s
                      WHERE s.ADDR LIKE :rgn || '%')
 GROUP BY i.ITEM_ID

실행계획 학습용 예시 ​

xplan
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |             |      1 |        |     61 |00:00:00.03 |    6053 |
|   1 |  HASH GROUP BY                           |             |      1 |     92 |     61 |00:00:00.03 |    6053 |
|*  2 |   FILTER                                 |             |      1 |        |    170 |00:00:00.03 |    6053 |
|   3 |    NESTED LOOPS                          |             |      1 |   3387 |   3381 |00:00:00.02 |    5509 |
|   4 |     NESTED LOOPS                         |             |      1 |   3387 |   3381 |00:00:00.01 |    4141 |
|   5 |      TABLE ACCESS BY INDEX ROWID BATCHED | ORD         |      1 |   1370 |   1368 |00:00:00.01 |      37 |
|*  6 |       INDEX RANGE SCAN                   | ORD_X01     |      1 |   1370 |   1368 |00:00:00.01 |       6 |
|*  7 |      INDEX RANGE SCAN                    | ORD_ITEM_PK |   1368 |      2 |   3381 |00:00:00.01 |    4104 |
|   8 |     TABLE ACCESS BY INDEX ROWID          | ORD_ITEM    |   3381 |      2 |   3381 |00:00:00.01 |    1368 |
|*  9 |    TABLE ACCESS BY INDEX ROWID           | SHOP        |    272 |      1 |     10 |00:00:00.01 |     544 |
|* 10 |     INDEX UNIQUE SCAN                    | SHOP_PK     |    272 |      1 |    272 |00:00:00.01 |     272 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "SHOP" "S" WHERE "S"."SHOP_NO"=:B1
              AND "S"."ADDR" LIKE :RGN||'%'))
   6 - access("O"."ORD_DT"=:DT)
   7 - access("I"."ORD_NO"="O"."ORD_NO")
   9 - filter("S"."ADDR" LIKE :RGN||'%')
  10 - access("S"."SHOP_NO"=:B1)

문제 ​

  1. 2번 줄 FILTER가 계획의 어디에 있는지, 그 때문에 어떤 일이 쓸데없이 일어났는지 줄 번호와 Starts로 설명하시오. 9번 줄의 Starts가 1,368이 아니라 272인 이유도 설명하시오.
  2. 서브쿼리의 결과는 그대로 두고, 서브쿼리를 처리하는 시점을 바꿔 일량을 줄이시오. 그 방법이 언제 유리하고 언제 불리한지도 쓰시오.
원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.
무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자