JOIN-009

점주 앱의 '오늘 주문' 일곱 건을 보여 주는데 주문을 3,389번 찾아간다

기초SELECT버거159 / 700

상황

점주 앱의 「오늘 주문 내역」은 매장 하나의 그날 주문과 주문 상품을 시각순으로 보여 준다. 점주 200명이 수시로 새로 고쳐서 하루 2만 번 호출된다. 개발자는 "주문상품에도 ORD_DT가 있어서 그걸로 먼저 걸렀다"라고 한다. 아래는 2013-06-14, 매장 SH0137(주문 7건, 주문상품 17줄)의 실행계획이다.

테이블과 인덱스

ORD 주문: 1,000,000행 · 22,422블록 · ORD_PK (ORD_NO) · ORD_X01 (ORD_DT, ORD_HMS) · 날짜순 적재 (하루 약 1,370건이 약 31블록에 모여 있다) · 매장 200개
ORD_ITEM 주문상품: 2,472,717행 · 55,265블록 · ORD_ITEM_PK (ORD_NO, ITEM_ID) · ORD_ITEM_X01 (ORD_DT, ORD_HMS) · 하루 약 3,390줄
ORD_ITEM.ORD_DT는 ORD에서 옮겨 적은 반정규화 컬럼이다 · 매장 번호 SHOP_NO는 ORD에만 있다 · 통계 최신

원본 SQL

sql

SELECT /*+ LEADING(i) USE_NL(o) */
       o.ORD_NO, o.ORD_HMS, i.ITEM_ID, i.ORD_ITEM_QTY
  FROM ORD_ITEM i, ORD o
 WHERE i.ORD_DT  = :v_dt
   AND o.ORD_NO  = i.ORD_NO
   AND o.ORD_DT  = :v_dt
   AND o.SHOP_NO = :v_shop
 ORDER BY o.ORD_HMS, i.ITEM_ID

실행계획 학습용 예시

xplan

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |              |      1 |        |     17 |00:00:00.03 |    3542 |
|   1 |  SORT ORDER BY                          |              |      1 |     17 |     17 |00:00:00.03 |    3542 |
|   2 |   NESTED LOOPS                          |              |      1 |     17 |     17 |00:00:00.03 |    3542 |
|   3 |    NESTED LOOPS                         |              |      1 |   3387 |   3389 |00:00:00.01 |     153 |
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED | ORD_ITEM     |      1 |   3387 |   3389 |00:00:00.01 |      91 |
|*  5 |      INDEX RANGE SCAN                   | ORD_ITEM_X01 |      1 |   3387 |   3389 |00:00:00.01 |      15 |
|*  6 |     INDEX UNIQUE SCAN                   | ORD_PK       |   3389 |      1 |   3389 |00:00:00.01 |      62 |
|*  7 |    TABLE ACCESS BY INDEX ROWID          | ORD          |   3389 |      1 |     17 |00:00:00.02 |    3389 |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("I"."ORD_DT"=:V_DT)
   6 - access("O"."ORD_NO"="I"."ORD_NO")
   7 - filter(("O"."ORD_DT"=:V_DT AND "O"."SHOP_NO"=:V_SHOP))

문제

7번 줄의 Starts와 A-Rows를 비교해, 이 조인이 주문을 몇 번 찾아가서 몇 건을 남기는지 설명하시오.
두 테이블의 조건이 각각 몇 행을 남기는지 비교해 드라이빙 테이블을 정하고 개선 SQL을 작성하시오.

개선 SQL원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.

개선 사유무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자

모범답안

모범 답안 SQL

sql

-- 그날 + 그 매장을 모두 거르는 ORD 를 먼저 읽고, 주문마다 ORD_ITEM_PK 로 주문상품을 찾아간다
SELECT /*+ LEADING(o i) USE_NL(i) */
       o.ORD_NO, o.ORD_HMS, i.ITEM_ID, i.ORD_ITEM_QTY
  FROM ORD o, ORD_ITEM i
 WHERE o.ORD_DT  = :v_dt
   AND o.SHOP_NO = :v_shop
   AND i.ORD_NO  = o.ORD_NO
   AND i.ORD_DT  = :v_dt
 ORDER BY o.ORD_HMS, i.ITEM_ID

개선 후 실행계획 학습용 예시

xplan

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |             |      1 |        |     17 |00:00:00.01 |      69 |
|   1 |  SORT ORDER BY                          |             |      1 |     17 |     17 |00:00:00.01 |      69 |
|   2 |   NESTED LOOPS                          |             |      1 |     17 |     17 |00:00:00.01 |      69 |
|   3 |    NESTED LOOPS                         |             |      1 |     17 |     17 |00:00:00.01 |      52 |
|*  4 |     TABLE ACCESS BY INDEX ROWID BATCHED | ORD         |      1 |      7 |      7 |00:00:00.01 |      38 |
|*  5 |      INDEX RANGE SCAN                   | ORD_X01     |      1 |   1370 |   1368 |00:00:00.01 |       7 |
|*  6 |     INDEX RANGE SCAN                    | ORD_ITEM_PK |      7 |      2 |     17 |00:00:00.01 |      14 |
|*  7 |    TABLE ACCESS BY INDEX ROWID          | ORD_ITEM    |     17 |      1 |     17 |00:00:00.01 |      17 |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("O"."SHOP_NO"=:V_SHOP)
   5 - access("O"."ORD_DT"=:V_DT)
   6 - access("I"."ORD_NO"="O"."ORD_NO")
   7 - filter("I"."ORD_DT"=:V_DT)

모범 답안 서술

7번 줄 TABLE ACCESS BY INDEX ROWID ORD의 Starts는 3,389이고 A-Rows는 17이다. ORD_ITEM_X01로 그날 주문상품 3,389줄을 먼저 읽고(3번 줄), 줄마다 ORD_PK로 주문을 찾아간 뒤 Predicate 7번의 매장 조건으로 99.5%를 버렸다. 주문을 3,389번 찾아가는 데서 Buffers 3,542 중 3,389가 나온다. 두 테이블의 조건이 남기는 행을 비교해 보자. 주문상품의 조건(그날)은 3,389줄을 남기지만, 주문의 조건(그날과 그 매장)은 7건만 남긴다. 매장 조건 SHOP_NO는 ORD에만 있어서 주문 쪽이 훨씬 많이 거른다. NL의 왕복 횟수는 드라이빙 집합의 크기로 정해지므로 ORD를 먼저 읽는다. ORD_X01로 그날 주문 1,368건을 읽어 매장 조건으로 7건을 남기고, 주문마다 ORD_ITEM_PK로 주문상품을 찾아간다. Buffers는 3,542 → 69로 준다. 하루 2만 번이면 약 7,084만 → 138만 블록이다. 주문상품에도 ORD_DT가 반정규화되어 있어서 개발자는 그 컬럼으로 상세를 먼저 거를 수 있다고 봤다. 하지만 그 조건은 그날의 주문상품을 전부 남길 뿐 매장을 거르지 못한다. 드라이빙은 조건이 '있는' 테이블이 아니라 조건을 적용한 뒤 '가장 적게 남는' 테이블로 정한다.

해설

매장 조건까지 인덱스로: 고친 계획에서도 ORD는 그날 주문 1,368건을 읽어 매장으로 거른다(4번 줄, 38블록). ORD (ORD_DT, SHOP_NO) 인덱스를 만들면 7건만 읽지만, 지금은 38블록이라 인덱스를 더 만들 만큼은 아니다.
반정규화 조건을 지울까: i.ORD_DT = :v_dt는 이제 7번 줄의 filter로만 쓰인다. 지워도 결과는 같아야 하지만, 두 컬럼 값이 늘 같은지(반정규화 컬럼의 정합성)를 먼저 대조한다.
힌트를 뗀다면: 통계가 맞으면 옵티마이저도 ORD를 먼저 읽는다. LEADING(i)는 E-Rows를 보고 내린 판단이 아니라 SQL을 쓸 때의 생각을 적은 것이므로, 남긴다면 근거를 힌트 유효기간 대장에 적는다(교재 06화 §8).
점주 앱처럼 짧게 자주 도는 화면은 한 번의 블록 수가 작아도 호출 횟수를 곱해서 본다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황