JOIN-002

하루 스무 건 출고 목록이 6천 블록을 읽는다

기초SELECT이커머스152 / 700

이 문제는 교재 18화 졸업 과제와 같은 이커머스 데이터베이스를 씁니다. 『튜너의 1년』을 순서대로 읽고 있다면 졸업 과제를 먼저 풀고 오세요. 아래 「테이블과 인덱스」 칸에 과제의 답 일부(제약 조건, 인덱스, 통계의 실제 상태)가 나옵니다.

상황

물류센터의 「출고 지시서」는 그날 들어온 주문의 상품 목록을 주문번호 순으로 뽑는다. 창고 단말 30대가 수시로 다시 뽑아서 하루 1만 2천 번 호출된다. 이 SQL은 구 쇼핑몰에서 옮겨 왔고, 맨 위의 힌트도 그대로 가져왔다. 개발자는 "구 시스템에서는 0.01초였다"라고 말한다.

테이블과 인덱스

orders: 34,908행 · 193블록 · 인덱스는 PK_ORDERS (order_id)뿐 · order_date는 DATE(시분초 있음) · 하루 평균 19건 (아래 실행계획은 2024-06-14, 21건)
order_items: 73,498행 · 290블록 · 인덱스는 PK_ORDER_ITEMS (order_item_id)뿐 · 주문당 평균 2.1줄
FK order_items.order_id → orders는 정의만 있고 꺼져 있다 (DISABLED NOT VALIDATED) · order_id에 인덱스 없음
구 쇼핑몰에는 order_items(order_id) 인덱스가 있었다 · 통계 최신

원본 SQL

sql

SELECT /*+ LEADING(o i) USE_NL(i) */
       o.order_id, o.order_date, i.product_id, i.quantity
  FROM orders o, order_items i
 WHERE o.order_date >= TO_DATE(:d, 'YYYYMMDD')
   AND o.order_date <  TO_DATE(:d, 'YYYYMMDD') + 1
   AND i.order_id = o.order_id
 ORDER BY o.order_id, i.product_id

실행계획 학습용 예시

xplan

-------------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |      1 |        |     44 |00:00:00.03 |    6283 |
|   1 |  SORT ORDER BY       |             |      1 |     40 |     44 |00:00:00.03 |    6283 |
|*  2 |   FILTER             |             |      1 |        |     44 |00:00:00.03 |    6283 |
|   3 |    NESTED LOOPS      |             |      1 |     40 |     44 |00:00:00.03 |    6283 |
|*  4 |     TABLE ACCESS FULL| ORDERS      |      1 |     19 |     21 |00:00:00.01 |     193 |
|*  5 |     TABLE ACCESS FULL| ORDER_ITEMS |     21 |      2 |     44 |00:00:00.03 |    6090 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(TO_DATE(:D,'YYYYMMDD')+1>TO_DATE(:D,'YYYYMMDD'))
   4 - filter(("O"."ORDER_DATE">=TO_DATE(:D,'YYYYMMDD') AND "O"."ORDER_DATE"<TO_DATE(:D,'YYYYMMDD')+1))
   5 - filter("I"."ORDER_ID"="O"."ORDER_ID")

문제

6,283블록 중 6,090블록이 어디서 나오는지 Starts와 블록 수로 설명하시오. Predicate Information 5번 줄에서 무엇을 알 수 있는지도 설명하시오.
개선안을 SQL(DDL 포함)로 작성하고, 구 시스템에서는 이 힌트가 왜 옳았는지와 지금은 무엇이 달라졌는지 서술하시오.

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

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

모범답안

모범 답안 SQL

sql

-- inner 쪽 조인 키에 인덱스 — 구 시스템의 힌트가 전제하고 있던 바로 그 인덱스
CREATE INDEX IX_ORDER_ITEMS_ORD ON order_items (order_id);
-- 드라이빙 쪽 하루치 주문도 인덱스로 찾는다 (선택)
CREATE INDEX IX_ORDERS_DT ON orders (order_date);

-- 문장은 그대로 — 이제 NL 이 맞는 판단이 됐다 (힌트를 떼도 옵티마이저가 같은 NL 을 고른다)
SELECT /*+ LEADING(o i) USE_NL(i) */
       o.order_id, o.order_date, i.product_id, i.quantity
  FROM orders o, order_items i
 WHERE o.order_date >= TO_DATE(:d, 'YYYYMMDD')
   AND o.order_date <  TO_DATE(:d, 'YYYYMMDD') + 1
   AND i.order_id = o.order_id
 ORDER BY o.order_id, i.product_id

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

xplan

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                    |      1 |        |     44 |00:00:00.01 |      88 |
|   1 |  SORT ORDER BY                          |                    |      1 |     40 |     44 |00:00:00.01 |      88 |
|*  2 |   FILTER                                |                    |      1 |        |     44 |00:00:00.01 |      88 |
|   3 |    NESTED LOOPS                         |                    |      1 |     40 |     44 |00:00:00.01 |      88 |
|   4 |     NESTED LOOPS                        |                    |      1 |     40 |     44 |00:00:00.01 |      65 |
|   5 |      TABLE ACCESS BY INDEX ROWID BATCHED| ORDERS             |      1 |     19 |     21 |00:00:00.01 |      23 |
|*  6 |       INDEX RANGE SCAN                  | IX_ORDERS_DT       |      1 |     19 |     21 |00:00:00.01 |       2 |
|*  7 |      INDEX RANGE SCAN                   | IX_ORDER_ITEMS_ORD |     21 |      2 |     44 |00:00:00.01 |      42 |
|   8 |     TABLE ACCESS BY INDEX ROWID         | ORDER_ITEMS        |     44 |      2 |     44 |00:00:00.01 |      23 |
----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(TO_DATE(:D,'YYYYMMDD')+1>TO_DATE(:D,'YYYYMMDD'))
   6 - access("O"."ORDER_DATE">=TO_DATE(:D,'YYYYMMDD') AND "O"."ORDER_DATE"<TO_DATE(:D,'YYYYMMDD')+1)
   7 - access("I"."ORDER_ID"="O"."ORDER_ID")

모범 답안 서술

5번 줄 TABLE ACCESS FULL ORDER_ITEMS의 Starts가 21이다. outer(4번 줄)의 A-Rows 21건마다 주문상세 290블록을 처음부터 끝까지 다시 읽은 것이다. 21 × 290 = 6,090블록이 이 SQL 일량의 97%를 차지한다. Predicate 5번은 filter("I"."ORDER_ID"="O"."ORDER_ID")다. 조인 조건이 access가 아니라 filter로 쓰였다. 찾아갈 인덱스가 없어서 매번 전부 읽고 거르는 것이다. 힌트 LEADING(o i) USE_NL(i)는 구 시스템에서는 옳았다. 거기에는 order_items(order_id) 인덱스가 있어서 주문 한 건마다 2~3블록이면 됐다. 이 이커머스 DB는 FK를 정의만 하고 꺼 두었고, FK를 만든다고 인덱스가 자동으로 생기지도 않는다. 결국 힌트가 전제한 인덱스는 두고 힌트만 옮겨 온 셈이다. 조인 키 order_items(order_id)에 인덱스를 만든다. outer가 하루 21건뿐이라 인덱스가 있으면 NL이 맞다. inner를 한 번 열 때 2~3블록이면 된다. 드라이빙 쪽 하루치 주문도 order_date 인덱스로 찾으면 Buffers는 6,283 → 88이 된다 (하루 1만 2천 번이면 약 7,540만 → 106만 블록). 인덱스를 당장 만들 수 없으면 NL 힌트를 떼거나 USE_HASH로 바꾼다. 해시 조인이면 두 테이블을 한 번씩만 읽어 193 + 290 ≈ 483블록이다.

해설

힌트는 전제와 함께 옮긴다. 이 힌트의 전제는 '주문상세에 주문번호 인덱스가 있다'였다. 힌트 유효기간 대장에 '전제한 인덱스 · 규모' 칸을 두는 것도 이 때문이다 (교재 06화 §8).
다른 정답: 인덱스 없이 해시 조인(약 483블록)으로 바꿔도 6,283블록보다 13배 낫다. 다만 하루 1만 2천 번이면 580만 블록 대 106만 블록이므로, 자주 호출되는 소량 화면에는 인덱스 NL이 맞다 (교재 06화 §5 손익분기).
FK와 인덱스: FK를 켜는 날 자식 FK 컬럼에 인덱스가 없으면, 부모 행을 삭제하거나 키를 바꿀 때 자식 테이블 전체에 잠금이 걸린다 (교재 12화). 그날을 위해서도 이 인덱스는 필요하다.
대가: 주문상세를 넣을 때마다 인덱스도 고친다. 운영에서는 INVISIBLE로 만들어 세션에서 재 보고 승인을 받는다 (교재 18화 T2: 주문 3만 5천 행을 적재할 때 인덱스 하나로 redo가 1.56 → 12MB가 됐다).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황