리스크 모니터가 상품마다 대량 구매 서너 건을 찾는데 주문상품을 170블록씩 읽는다

기초SELECT이커머스108 / 700

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

상황

리스크팀의 「대량 구매 모니터」는 5분마다 상품 280개를 차례로 돌며, 한 번에 10개 이상 산 주문상품 줄을 찾는다. 재판매 업자를 걸러 내려는 것이다. 상품 하나에 한 번씩이니 하루 약 8만 번 실행된다. 상품 상세 화면용으로 만든 product_id 인덱스를 타는데, 상품 하나에서 서너 줄을 찾는 데 170블록 넘게 읽는다. 아래는 상품 57이다.

테이블과 인덱스

order_items 주문상품: 73,498행 · 290블록(블록당 약 253행) · order_item_id 순으로 쌓였다(주문이 들어온 순서) · 한 상품의 줄은 테이블 전체에 흩어져 있다
product_id 280종(상품당 약 262줄) · 이 문제에서는 quantity가 대부분 1 ~ 5이고 10개 이상은 약 1%라고 둔다
상품 57: 262줄이 172블록에 흩어져 있다 · 그중 quantity >= 10은 3줄
인덱스 IX_OI_PROD (product_id): 상품 상세 화면용 · BLEVEL 1 · 리프 약 160 · CLUSTERING_FACTOR 약 72,000 · 통계 최신 · 주문이 들어올 때마다 주문상품이 INSERT된다

원본 SQL

sql

SELECT order_item_id, order_id, quantity, unit_price
  FROM order_items
 WHERE product_id = :p
   AND quantity  >= 10

실행계획 학습용 예시 · quantity 분포는 문제에 적은 가정

xplan

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |               |      1 |        |      3 |00:00:00.01 |     174 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED | ORDER_ITEMS   |      1 |      3 |      3 |00:00:00.01 |     174 |
|*  2 |   INDEX RANGE SCAN                   | IX_OI_PROD    |      1 |    262 |    262 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("QUANTITY">=10)
   2 - access("PRODUCT_ID"=:P)

문제

3줄을 찾는데 테이블을 172블록 읽는 이유를 Predicate Information의 1번과 2번 줄로 설명하시오.
테이블에 가는 횟수가 결과 건수만큼으로 줄도록 인덱스를 고치고 DDL을 작성하시오. 기존 인덱스를 어떻게 할지와 그 대가도 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 상품으로 찾고 · 수량 조건까지 인덱스에서 거른다 → 테이블에는 결과 3줄만 간다
CREATE INDEX IX_OI_PROD_QTY ON order_items (product_id, quantity) INVISIBLE;
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;   -- 세션에서 모니터 SQL 과 상품 상세 SQL 을 재고 → VISIBLE

SELECT order_item_id, order_id, quantity, unit_price        -- SQL 은 그대로
  FROM order_items
 WHERE product_id = :p
   AND quantity  >= 10;

-- 기존 (product_id) 는 새 인덱스의 접두부 · 상품 상세 화면도 새 인덱스로 재 본 뒤 INVISIBLE → DROP
ALTER INDEX IX_OI_PROD INVISIBLE;

개선 후 실행계획 학습용 예시 · quantity 분포는 문제에 적은 가정

xplan

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |      1 |        |      3 |00:00:00.01 |       5 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED | ORDER_ITEMS    |      1 |      3 |      3 |00:00:00.01 |       5 |
|*  2 |   INDEX RANGE SCAN                   | IX_OI_PROD_QTY |      1 |      3 |      3 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------------------------
   2 - access("PRODUCT_ID"=:P AND "QUANTITY">=10)

모범 답안 서술

2번 줄 access에는 상품 조건 "PRODUCT_ID"=:P만 있고, 수량 조건은 1번 줄 테이블 액세스의 filter에 있다. quantity가 인덱스에 없어서, 인덱스로 상품 57의 262줄을 찾은 뒤 262줄 모두 테이블에 가서 수량을 확인하고 3줄만 남겼다. 그 262줄은 172블록에 흩어져 있다. 주문상품은 주문이 들어온 순서로 쌓이므로 한 상품의 줄이 테이블 전체에 퍼져 있다(CF 약 72,000). 그래서 3줄을 찾는 데 테이블을 172블록 읽었다(Buffers 174). 하루 8만 번이면 약 1,400만 블록이다. 상품 번호 뒤에 quantity를 붙인 (product_id, quantity) 인덱스를 만들면 =인 상품 조건과 수량 범위가 모두 access가 되어, 리프에서 상품 57의 '10개 이상' 항목 3개만 읽는다. 테이블에는 결과 3줄만 가므로 Buffers는 174 → 5다. 하루 8만 번이면 약 1,400만 → 40만 블록이다. 기존 (product_id) 인덱스는 새 인덱스의 접두부라서 상품 상세 화면도 새 인덱스로 처리할 수 있다. INVISIBLE 단계에서 두 SQL을 모두 재 본 뒤 기존 인덱스를 지우면 인덱스 수가 늘지 않는다. 대가는 주문상품을 INSERT할 때 고치는 인덱스 항목이 수량 한 컬럼만큼 커진다는 것뿐이다.

해설

함정: 테이블 필터 조건이 남는 행을 크게 줄일 때(262 → 3) 그 컬럼을 인덱스로 옮기는 효과가 가장 크다. 남는 행이 절반쯤이면 블록이 그만큼 줄지 않는다(ACC-031).
SELECT 목록까지 다 담은 커버링 (product_id, quantity, order_item_id, order_id, unit_price)도 답이 된다. 테이블 5블록 중 3블록을 더 아끼지만 리프가 두 배 가까이 커진다. 결과가 서너 줄이면 필터 컬럼만 옮기는 것으로 충분하다.
(quantity, product_id) 순서로 만들면 10개 이상인 줄 전 상품 약 730개를 읽고 상품으로 거른다. =로 들어오는 product_id를 앞에 둔다(ACC-001).
상품 280개를 280번 돌지 않고 quantity >= 10인 줄을 한 번에 뽑아 상품별로 묶으면 실행이 288번(5분마다 한 번)으로 준다. 그 SQL에는 (quantity) 선두 인덱스가 맞다. 모니터를 그렇게 바꿀 수 있는지 리스크팀과 먼저 정한다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · quantity 분포는 문제에 적은 가정 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · quantity 분포는 문제에 적은 가정 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황