SQLP 튜닝 연습장

테마

튜닝 연습인덱스 스캔 효율과 테이블 액세스
ACC-071

리뷰 탭을 붙인 뒤로 상품 상세 한 번에 리뷰 테이블을 두 번 통째로 읽는다

실전DDL이커머스146 / 700

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

상황 ​

이커머스 쇼핑몰이 상품 상세 화면에 리뷰 탭을 붙였다. 상세 화면이 열릴 때마다 「최근 리뷰 10건」(R1)과 「평점 요약」(R2)이 함께 돌고, 상세 화면은 하루 약 4만 번 열린다. 같은 배포에 「내 리뷰」(R3, 하루 약 2,000번)와 운영자용 「낮은 평점 검토」(R4, 하루 서너 번)도 들어갔다. reviews에는 PK만 있다. 개발팀은 조건에 나오는 컬럼마다 하나씩 (product_id), (customer_id), (rating), (created_at) 넷을 요청했다. 박팀장은 화면마다 몇 블록인지 따져서 최소로 정해 오라고 했다.

테이블과 인덱스 ​

  • reviews: 15,000행 · 775블록(블록당 약 19행: content CLOB이 대부분 4,000바이트보다 짧아 행 안에 저장된다) · review_id 순으로 쌓였다
  • product_id 280종(상품당 34 ~ 76건, 평균 약 54건) · customer_id 5,230명(고객당 평균 2.9건) · rating 1 ~ 5(평점 1은 약 6%) · created_at DATE(2020 ~ 2024, 하루 약 8건)
  • 한 상품의 리뷰도, 한 고객의 리뷰도 들어온 순서대로 테이블 전체에 흩어져 있다 · 예시 상품 57은 리뷰 62건
  • 인덱스: PK (review_id)만(PK_REVIEWS) · FK는 꺼져 있다
  • 어림: 항목 하나는 '컬럼 값 + 컬럼마다 1바이트 + ROWID 7바이트 + 머리 약 4바이트' · 블록당 약 7,200바이트 · product_id와 customer_id는 3바이트, rating은 2바이트, DATE는 7바이트

원본 SQL ​

sql
-- R1 「최근 리뷰 10건」 · 상품 상세 · 하루 약 40,000번
SELECT review_id, customer_id, rating, content, created_at
  FROM reviews
 WHERE product_id = :p
 ORDER BY created_at DESC
 FETCH FIRST 10 ROWS ONLY;

-- R2 「평점 요약」 · 상품 상세 · 하루 약 40,000번
SELECT COUNT(*) AS cnt, ROUND(AVG(rating), 1) AS avg_rating
  FROM reviews
 WHERE product_id = :p;

-- R3 「내 리뷰」 · 마이페이지 · 하루 약 2,000번
SELECT review_id, product_id, rating, created_at
  FROM reviews
 WHERE customer_id = :c
 ORDER BY created_at DESC;

-- R4 「낮은 평점 검토」 · 운영자 · 하루 서너 번
SELECT review_id, product_id, customer_id, rating, created_at
  FROM reviews
 WHERE rating = 1
   AND created_at >= TRUNC(SYSDATE) - 7;

실행계획 학습용 예시 · 행 수와 블록은 ora-newshop 실측 ​

xplan
[R1 · 상품 57]
----------------------------------------------------------------------------------------------
| Id  | Operation                | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |         |      1 |        |     10 |00:00:00.01 |     775 |
|*  1 |  VIEW                    |         |      1 |     10 |     10 |00:00:00.01 |     775 |
|*  2 |   WINDOW SORT PUSHED RANK|         |      1 |     54 |     10 |00:00:00.01 |     775 |
|*  3 |    TABLE ACCESS FULL     | REVIEWS |      1 |     54 |     62 |00:00:00.01 |     775 |
----------------------------------------------------------------------------------------------
   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=10)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("CREATED_AT") DESC )<=10)
   3 - filter("PRODUCT_ID"=:P)

[R2 · 상품 57]
----------------------------------------------------------------------------------------------
| Id  | Operation                | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |         |      1 |        |      1 |00:00:00.01 |     775 |
|   1 |  SORT AGGREGATE          |         |      1 |      1 |      1 |00:00:00.01 |     775 |
|*  2 |   TABLE ACCESS FULL      | REVIEWS |      1 |     54 |     62 |00:00:00.01 |     775 |
----------------------------------------------------------------------------------------------
   2 - filter("PRODUCT_ID"=:P)

[R3 · R4]  둘 다 TABLE ACCESS FULL · Buffers 775 (R3은 SORT ORDER BY가 붙는다)

문제 ​

  1. 지금 R1 ~ R4가 하루에 읽는 블록을 계산하고, 요청서의 인덱스 넷으로 각각 몇 블록이 될지 어림하시오.
  2. 최소 인덱스 세트를 설계해 DDL을 작성하시오. 컬럼 순서를 그렇게 정한 이유를 R1의 정렬과 테이블 액세스로 설명하시오.
  3. 만들지 않는 인덱스와 그 이유, 고른 세트의 대가를 쓰시오.
원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.
무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자