INDEX_SS 힌트를 붙였는데도 「내가 쓴 리뷰」가 리뷰 1만 5천 건을 다 읽는다

기초SELECT이커머스63 / 700

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

상황

이커머스 앱 마이페이지의 「내가 쓴 리뷰」는 로그인한 고객이 쓴 리뷰를 최신순으로 보여 준다. 하루 약 2만 번 열린다. 개발자는 상품 상세 화면용으로 만든 IX_REVIEWS_PROD_DT를 이 화면에도 쓰려고 INDEX_SS 힌트를 붙였다. 메모에는 "선두 컬럼이 빠져도 SKIP SCAN이면 인덱스를 탈 수 있다고 배웠다"라고 적혀 있다. 그런데 계획은 FULL 그대로다. 아래는 고객 1042(리뷰 5건)로 연 계획이다.

테이블과 인덱스

reviews: 15,000행 · 775블록 · review_id PK · product_id(상품 280개) · customer_id(리뷰를 쓴 고객 약 4,000명, 고객당 평균 3.8건, 많아야 14건) · rating · content CLOB(행 안에 저장) · created_at DATE
한 고객의 리뷰는 쓴 날짜가 달라 테이블 곳곳의 서로 다른 블록에 있다
인덱스 PK_REVIEWS (review_id) · IX_REVIEWS_PROD_DT (product_id, created_at): IDX-046에서 상품 상세 화면용으로 만든 인덱스 · BLEVEL 1 · 리프 약 45블록 · 통계 최신
리뷰는 하루 약 40건씩 늘고, 고치거나 지우는 일은 드물다

원본 SQL

sql

SELECT /*+ INDEX_SS(r IX_REVIEWS_PROD_DT) */
       r.review_id, r.product_id, r.rating, r.created_at
  FROM reviews r
 WHERE r.customer_id = :v_cust
 ORDER BY r.created_at DESC

실행계획 학습용 예시

xplan

-----------------------------------------------------------------------------------------
| Id  | Operation          | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |      1 |        |      5 |00:00:00.01 |     775 |
|   1 |  SORT ORDER BY     |         |      1 |      4 |      5 |00:00:00.01 |     775 |
|*  2 |   TABLE ACCESS FULL| REVIEWS |      1 |      4 |      5 |00:00:00.01 |     775 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("R"."CUSTOMER_ID"=:V_CUST)

문제

INDEX_SS 힌트를 붙였는데도 INDEX SKIP SCAN이 나오지 않고 테이블 전체를 읽는 이유를, SKIP SCAN이 가능하려면 무엇이 있어야 하는지로 설명하시오.
이 화면이 인덱스 범위 스캔으로 처리되도록 인덱스와 SQL을 고치고, 정렬까지 없어지는지 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 고객 번호를 선두로, 작성일을 뒤에 둔다 (역순으로 읽으면 ORDER BY 정렬도 없다)
CREATE INDEX IX_REVIEWS_CUST_DT ON reviews (customer_id, created_at);

-- INDEX_SS 힌트를 뗀다 (조건과 정렬은 그대로)
SELECT review_id, product_id, rating, created_at
  FROM reviews
 WHERE customer_id = :v_cust
 ORDER BY created_at DESC;

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

xplan

------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                    |      1 |        |      5 |00:00:00.01 |       7 |
|   1 |  TABLE ACCESS BY INDEX ROWID | REVIEWS            |      1 |      4 |      5 |00:00:00.01 |       7 |
|*  2 |   INDEX RANGE SCAN DESCENDING| IX_REVIEWS_CUST_DT |      1 |      4 |      5 |00:00:00.01 |       2 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("CUSTOMER_ID"=:V_CUST)

모범 답안 서술

SKIP SCAN은 선두 컬럼의 값마다 인덱스를 작은 덩어리로 보고, 덩어리마다 뒤 컬럼의 조건 범위로 찾아 들어가는 방식이다. IX_REVIEWS_PROD_DT라면 상품 280개마다 들어가서 "그 상품 안에서 조건에 맞는 범위"를 찾는다. 그러려면 조건 컬럼이 인덱스의 뒤 컬럼으로 들어 있어야 한다. 이 SQL의 조건 컬럼 customer_id는 이 인덱스에 들어 있지 않다. 상품마다 들어가도 찾을 값이 없으므로 SKIP SCAN을 할 수 없고, 힌트는 쓰이지 않은 채 무시되었다. 그래서 2번 줄처럼 테이블 15,000행을 다 읽어 5건을 거르고(775블록), 1번 줄에서 정렬한다. 고객 번호를 선두로 한 (customer_id, created_at) 인덱스를 만들고 힌트를 뗀다. 고객 한 명의 항목(많아야 14개)은 리프 한 블록 안에 모여 있어 인덱스는 2블록, 테이블은 리뷰 수만큼 5블록을 읽는다(Buffers 775 → 7). 같은 고객 안에서 항목이 작성일 순으로 놓여 있으므로 역순으로 읽으면(INDEX RANGE SCAN DESCENDING) 결과가 이미 최신순이라 SORT ORDER BY도 없어진다. 대가는 리뷰 15,000행짜리 인덱스 하나(리프 약 45블록)다. 리뷰는 하루 40건쯤 들어오므로 고칠 항목도 하루 40개 남짓이고, 하루 2만 번 775블록씩 읽던 일(약 1,550만 블록)과 바꿀 만하다.

해설

함정: (product_id, created_at, customer_id)처럼 뒤에 customer_id를 덧붙이면 SKIP SCAN은 가능해진다. 하지만 선두 상품 280개마다 한 번씩 들어가야 하므로 수백 블록을 읽는다. SKIP SCAN은 선두 값의 종류가 적을 때만 싸다(교재 02화 §2). 고객이 조건의 주인공이면 고객을 선두로 둔다.
19c의 DBMS_XPLAN은 힌트 보고서(Hint Report)로 쓰이지 않은 힌트를 따로 보여 준다. 힌트를 붙였는데 계획이 그대로면 힌트가 무시된 것인지부터 확인한다.
이 조회는 고객 번호 하나로 몇 건을 찾는 전형적인 등치 조회다. 리뷰가 쌓여 10만 건이 되어도 새 인덱스로는 고객 한 명의 리뷰 수만큼만 읽는다. FULL은 테이블이 커지는 만큼 같이 커진다.
content가 행 안에 저장된 CLOB이라 행이 길어서, 15,000행인데도 775블록이나 된다. 이런 테이블일수록 FULL과 인덱스 범위 스캔의 차이가 크다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황