「최근 리뷰 10건」은 12블록인데 상단 배지 「마지막 5점 리뷰」가 59블록을 읽는다

중급SELECT이커머스315 / 700

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

상황

SORT-002에서 상품 상세의 「최근 리뷰 10건」을 인덱스로 고친 뒤 그 칸은 12블록으로 줄었다. 이번에 상품팀이 상세 상단에 「마지막 5점 리뷰: 3일 전」 배지를 더했다. 상품 상세는 하루 약 8만 번 열린다. 배지 SQL은 MAX 한 줄인데 실행계획을 보니 리뷰 칸보다 다섯 배쯤 무겁다. 개발자는 "MAX는 인덱스 끝만 읽는다고 배웠다"라고 한다. 다음 달에는 「마지막 1점 리뷰」 배지도 붙일 예정이다.

테이블과 인덱스

reviews: 15,000행 · 775블록 · 블록당 약 19행(content CLOB) · 상품당 34 ~ 76건 · 상품 100은 57건이고 테이블 곳곳의 블록에 흩어져 있다
rating 1 ~ 5: 5점 약 38% · 1점 약 6% · 상품 100의 5점 리뷰는 22건이다
인덱스 IX_REV_PROD_DT (product_id, created_at, review_id): SORT-002에서 만든 인덱스 · BLEVEL 1 · 리프 약 60블록 · PK(review_id) · 통계 최신

원본 SQL

sql

SELECT MAX(created_at) AS last_5star
  FROM reviews
 WHERE product_id = :prod
   AND rating = 5

실행계획 학습용 예시 · 교재 18화 E4 규모

xplan

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |      1 |        |      1 |00:00:00.01 |      59 |
|   1 |  SORT AGGREGATE                      |                |      1 |      1 |      1 |00:00:00.01 |      59 |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| REVIEWS        |      1 |     11 |     22 |00:00:00.01 |      59 |
|*  3 |    INDEX RANGE SCAN                  | IX_REV_PROD_DT |      1 |     54 |     57 |00:00:00.01 |       2 |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("RATING"=5)
   3 - access("PRODUCT_ID"=:PROD)

[참고 : 같은 인덱스 · 조건이 product_id 하나일 때]
  SELECT MAX(created_at) FROM reviews WHERE product_id = :prod
  → SORT AGGREGATE · FIRST ROW · INDEX RANGE SCAN (MIN/MAX) IX_REV_PROD_DT · Buffers 2

문제

같은 인덱스로 MAX(created_at)가 2블록에 끝나는 경우와 59블록을 읽는 이 SQL의 차이를 실행계획 2번, 3번 줄로 설명하시오.
새 인덱스를 더하는 안, 기존 인덱스에 컬럼을 덧붙이는 안, 인덱스는 그대로 두고 SQL만 바꾸는 안을 비교하고 하나를 골라 SQL과 DDL로 쓰시오. 1점 리뷰 배지가 생기면 각 안이 어떻게 되는지도 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 기존 인덱스 끝에 rating 을 덧붙인다 : 앞 세 컬럼이 같아서 「최근 리뷰 10건」(SORT-002) 도 그대로
CREATE INDEX IX_REV_PROD_DT_RT ON reviews (product_id, created_at, review_id, rating) INVISIBLE;
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;   -- 두 SQL 을 세션에서 재 본 뒤
ALTER INDEX IX_REV_PROD_DT_RT VISIBLE;
DROP INDEX IX_REV_PROD_DT;

-- SQL 은 그대로
SELECT MAX(created_at) AS last_5star
  FROM reviews
 WHERE product_id = :prod
   AND rating = 5

개선 후 실행계획 학습용 예시 · 교재 18화 E4 규모

xplan

---------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |      1 |        |      1 |00:00:00.01 |       2 |
|   1 |  SORT AGGREGATE              |                   |      1 |      1 |      1 |00:00:00.01 |       2 |
|   2 |   FIRST ROW                  |                   |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  3 |    INDEX RANGE SCAN (MIN/MAX)| IX_REV_PROD_DT_RT |      1 |      1 |      1 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("PRODUCT_ID"=:PROD)
       filter("RATING"=5)

모범 답안 서술

조건이 product_id 하나뿐이면 같은 인덱스에서 FIRST ROW와 INDEX RANGE SCAN (MIN/MAX)로 상품 범위의 오른쪽 끝 항목 하나만 읽고 2블록에 끝난다. 이 SQL에는 rating = 5가 붙었는데, rating은 인덱스에 없는 컬럼이다. 끝 항목 하나를 읽어도 그 리뷰가 5점인지는 테이블에 가 봐야 안다. 그래서 오라클은 FIRST ROW를 쓰지 못하고, 3번 줄이 상품 100의 항목 57건을 다 읽고 2번 줄이 테이블을 57번 찾아가 5점 22건을 거른 뒤 1번 줄이 그중 최댓값을 고른다. 한 상품의 리뷰가 테이블 곳곳에 흩어져 있어 테이블에서 57블록, 합계 59블록이다. 하루 8만 번이면 약 470만 블록이다. 세 안을 비교한다. ① (product_id, rating, created_at) 인덱스를 새로 만들면 등치 조건 둘 뒤에 created_at이 와서 FIRST ROW가 항목 하나로 끝난다(2블록). 다만 인덱스가 하나 더 늘어 리뷰 INSERT마다 고칠 인덱스가 둘이 된다. ② 기존 인덱스 끝에 rating을 덧붙여 (product_id, created_at, review_id, rating)으로 바꾸면, FIRST ROW가 오른쪽 끝에서부터 5점 항목을 만날 때까지 인덱스 안에서만 거슬러 올라간다. 5점이 38%라 평균 세 항목 안에서 끝난다(2블록). 앞 세 컬럼이 같아서 「최근 리뷰 10건」도 그대로 처리되고 인덱스 개수도 늘지 않으며, 인덱스 크기는 항목마다 몇 바이트 늘 뿐이다. ③ 인덱스를 그대로 두고 ORDER BY created_at DESC FETCH FIRST 1 ROW ONLY로 바꾸면 최신순으로 테이블을 찾아가다 첫 5점에서 멈춘다. 5점은 평균 세 건 안팎(약 5블록)이라 새 인덱스 없이도 쓸 만하다. 1점 배지가 생기면 갈린다. 1점은 6%라서 ③은 첫 1점을 만날 때까지 평균 17건 안팎을 테이블에서 읽고, 1점 리뷰가 없는 상품이면 57건을 모두 읽는다(약 59블록). ②는 같은 일을 리프 안에서 하므로 2 ~ 3블록이면 끝나고, ①은 평점마다 항목 하나다. 그래서 ②를 고른다. 리뷰 칸과 두 배지를 인덱스 하나로 처리하고, 평점이 무엇이든 인덱스 안에서 끝난다. Buffers는 59 → 2로 준다.

해설

FIRST ROW의 조건: 조건절의 컬럼과 MIN/MAX 인자 컬럼이 모두 인덱스에 있어야 한다. 테이블에서 확인해야 하는 조건이 하나라도 있으면 끝 항목 하나로 답을 낼 수 없다.
②에서 rating은 access가 아니라 filter로 걸린다. 그래도 인덱스 안의 filter라 테이블에 가지 않는다. 같은 FIRST ROW라도 거슬러 올라가는 항목 수는 분포에 달려 있다는 점을 알고 쓴다.
③은 결과 모양이 다르다. 5점 리뷰가 없는 상품이면 MAX는 NULL 한 줄을, FETCH FIRST 1 ROW ONLY는 0줄을 돌려준다. 화면 코드가 둘 중 무엇을 기대하는지 확인한다.
교재 18화 E4에서 만든 인덱스는 (product_id, created_at, rating)이었다. 그 인덱스라면 이 배지도 FIRST ROW로 끝나지만, 동점 깨기 컬럼(review_id)이 없어 SORT-002의 정렬 키 유일성은 따로 챙겨야 한다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 교재 18화 E4 규모 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 교재 18화 E4 규모 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황