SQLP 튜닝 연습장

테마

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

매장 단체 주문 알림을 새로 고칠 때마다 2초씩 걸리고 TEMP를 쓴다

중급SELECT버거263 / 700

상황 ​

매장 운영팀의 「단체 주문 알림」은 오늘 들어온 주문 가운데 상품을 4종 이상 담은 주문을 매장별로 센다. 매장 지원 담당자들이 하루 수십 번 새로 고치는데, 한 번에 2초 가까이 걸리고 그때마다 TEMP를 30MB 넘게 쓴다. 개발자는 "IN 서브쿼리는 오라클이 알아서 조인으로 펼쳐 준다고 들었다"라고 한다.

테이블과 인덱스 ​

  • ORD 주문: 1,000,000행 · 22,422블록 · ORD_PK (ORD_NO) · ORD_X01 (ORD_DT, ORD_HMS) (하루치가 약 31블록에 모여 있다)
  • ORD_ITEM 주문상품: 2,472,717행 · 55,265블록 · ORD_ITEM_PK (ORD_NO, ITEM_ID) (리프 약 6,200블록, 높이 3) · ORD_ITEM_X01 (ORD_DT, ORD_HMS) (하루치가 약 76블록에 모여 있다) · 주문당 1~4종 · ORD_DT는 주문 헤더의 날짜를 그대로 복사한 값이다
  • 2013-03-15 주문 1,368건 · 그날 주문상품 3,381줄 · 그중 4종 주문 320건(매장 161곳) · 전 기간 4종 주문 약 23만 1천 건
  • PGA 작업 영역이 작게 잡혀 있다 · 통계 최신

원본 SQL ​

sql
SELECT o.SHOP_NO, COUNT(*) AS big_ord_cnt
  FROM ORD o
 WHERE o.ORD_DT = :dt
   AND o.ORD_NO IN (SELECT i.ORD_NO
                      FROM ORD_ITEM i
                     GROUP BY i.ORD_NO
                    HAVING COUNT(*) >= 4)
 GROUP BY o.SHOP_NO

실행계획 학습용 예시 ​

xplan
--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads | Used-Mem | Used-Tmp |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |             |      1 |        |    161 |00:00:01.94 |    6268 |  4113 |          |          |
|   1 |  HASH GROUP BY                        |             |      1 |    200 |    161 |00:00:01.94 |    6268 |  4113 | 1155K (0)|          |
|*  2 |   HASH JOIN                           |             |      1 |   1370 |    320 |00:00:01.94 |    6268 |  4113 | 1264K (0)|          |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| ORD         |      1 |   1370 |   1368 |00:00:00.01 |      37 |     0 |          |          |
|*  4 |     INDEX RANGE SCAN                  | ORD_X01     |      1 |   1370 |   1368 |00:00:00.01 |       6 |     0 |          |          |
|   5 |    VIEW                               | VW_NSO_1    |      1 |  50000 |    231K|00:00:01.90 |    6231 |  4113 |          |          |
|*  6 |     HASH GROUP BY                     |             |      1 |  50000 |    231K|00:00:01.88 |    6231 |  4113 |   38M (1)|    33M   |
|   7 |      INDEX FAST FULL SCAN             | ORD_ITEM_PK |      1 |   2472K|   2472K|00:00:00.42 |    6231 |     0 |          |          |
--------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("O"."ORD_NO"="ORD_NO")
   4 - access("O"."ORD_DT"=:DT)
   6 - filter(COUNT(*)>=4)

문제 ​

  1. 5번에서 7번 줄이 무엇을 만들었는지, 오늘 주문 1,368건과 비교해 설명하시오. TEMP를 쓰는 줄과 그 이유도 설명하시오.
  2. 결과가 바뀌지 않게 SQL을 고치고, 결과가 같다는 근거를 쓰시오. 다른 방법이 있다면 그 일량과 함께 쓰시오.
원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.
무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자