상품 280개짜리 평점 요약표가 리뷰 테이블을 560번 읽는다

중급SELECT이커머스247 / 700

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

상황

MD팀의 「카테고리별 평점 현황」은 카테고리마다 상품 수, 리뷰 수, 상품 평점의 평균을 보여 준다. 기획 회의 때마다 여러 번 돌리는데 한 번에 1.1초 걸린다. 개발자는 "19c부터는 옵티마이저가 스칼라 서브쿼리를 알아서 조인으로 펼친다고 들었는데, 계획에는 서브쿼리가 그대로 남아 있다"라고 했다.

테이블과 인덱스

products: 280행 · 8블록 · PK만 있음 · 상품이 달린 카테고리는 40개
reviews: 15,000행 · 775블록 · PK(review_id)만 있음 · 상품당 34 ~ 76건 · product_id에는 인덱스가 없다
스칼라 서브쿼리 Unnesting은 켜져 있다(기본값) · 통계 최신

원본 SQL

sql

SELECT p.category_id,
       COUNT(*) AS product_cnt,
       SUM((SELECT COUNT(*) FROM reviews r WHERE r.product_id = p.product_id)) AS review_cnt,
       ROUND(AVG((SELECT AVG(r.rating) FROM reviews r WHERE r.product_id = p.product_id)), 2) AS avg_rating
  FROM products p
 GROUP BY p.category_id
 ORDER BY p.category_id

실행계획 학습용 예시

xplan

--------------------------------------------------------------------------------------------
| Id  | Operation          | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |      1 |        |     40 |00:00:01.12 |     434K|
|   1 |  SORT AGGREGATE    |          |    280 |      1 |    280 |00:00:00.55 |     217K|
|*  2 |   TABLE ACCESS FULL| REVIEWS  |    280 |     54 |  15000 |00:00:00.54 |     217K|
|   3 |  SORT AGGREGATE    |          |    280 |      1 |    280 |00:00:00.56 |     217K|
|*  4 |   TABLE ACCESS FULL| REVIEWS  |    280 |     54 |  15000 |00:00:00.55 |     217K|
|   5 |  SORT GROUP BY     |          |      1 |     40 |     40 |00:00:01.12 |     434K|
|   6 |   TABLE ACCESS FULL| PRODUCTS |      1 |    280 |    280 |00:00:00.01 |       8 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("R"."PRODUCT_ID"=:B1)
   4 - filter("R"."PRODUCT_ID"=:B1)

(DBMS_XPLAN 이 줄여 쓴 값 — 217K = 217,000 · 434K = 434,008)

문제

1번과 3번 줄의 Starts와 Buffers로 리뷰 테이블을 몇 번, 몇 블록 읽었는지 계산하시오. Unnesting이 켜져 있는데도 스칼라 서브쿼리가 펼쳐지지 않은 이유를 설명하시오.
결과가 바뀌지 않게 SQL을 고치시오. 리뷰가 없는 상품이 생겨도 원래와 같은 답이 나오도록 하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 두 단계를 SQL 로 직접 쓴다 : 리뷰를 상품별로 한 번 묶고 → 상품에 외부 조인 → 카테고리별로 묶는다
SELECT p.category_id,
       COUNT(*)                    AS product_cnt,
       SUM(NVL(r.review_cnt, 0))   AS review_cnt,
       ROUND(AVG(r.avg_rating), 2) AS avg_rating
  FROM products p,
       (SELECT product_id, COUNT(*) AS review_cnt, AVG(rating) AS avg_rating
          FROM reviews
         GROUP BY product_id) r
 WHERE r.product_id(+) = p.product_id
 GROUP BY p.category_id
 ORDER BY p.category_id;

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

xplan

------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |      1 |        |     40 |00:00:00.01 |     783 |          |
|   1 |  SORT GROUP BY        |          |      1 |     40 |     40 |00:00:00.01 |     783 | 6144 (0) |
|*  2 |   HASH JOIN OUTER     |          |      1 |    280 |    280 |00:00:00.01 |     783 | 1328K (0)|
|   3 |    TABLE ACCESS FULL  | PRODUCTS |      1 |    280 |    280 |00:00:00.01 |       8 |          |
|   4 |    VIEW               |          |      1 |    280 |    280 |00:00:00.01 |     775 |          |
|   5 |     HASH GROUP BY     |          |      1 |    280 |    280 |00:00:00.01 |     775 | 1218K (0)|
|   6 |      TABLE ACCESS FULL| REVIEWS  |      1 |  15000 |  15000 |00:00:00.01 |     775 |          |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("R"."PRODUCT_ID"(+)="P"."PRODUCT_ID")

모범 답안 서술

1번과 3번 줄은 리뷰 수와 평균 평점을 구하는 스칼라 서브쿼리다. 상품마다(Starts 280) 리뷰 테이블을 처음부터 끝까지 읽었다. product_id에 인덱스가 없어 한 번에 775블록이므로, 서브쿼리 하나에 280 × 775 = 217,000블록, 둘이면 434,000블록이다. 상품번호는 행마다 달라 캐시가 줄여 줄 것도 없다. Unnesting이 켜져 있는데도 펼쳐지지 않은 것은 두 서브쿼리가 바깥 GROUP BY 쿼리의 집계 함수(SUM, AVG) 안에 있기 때문이다. 서브쿼리를 조인으로 펼치려면 상품 단위 묶음과 카테고리 단위 묶음을 두 단계로 나눠야 하는데, 옵티마이저는 이 모양을 펼치지 않는다(교재 08화 10053 트레이스의 Outer query has illegal group-by). 그 두 단계를 SQL로 직접 쓴다. 리뷰를 상품별로 한 번 묶고(775블록), 상품 280행에 외부 조인으로 붙인 뒤 카테고리별로 묶는다. Buffers는 434K(434,008) → 783, A-Time은 1.12 → 0.01초로 준다. 리뷰가 없는 상품이 생기면 스칼라 서브쿼리의 COUNT는 0, AVG는 NULL을 돌려준다. 외부 조인으로 붙인 뷰에서는 둘 다 NULL이므로, 리뷰 수는 NVL(r.review_cnt, 0)으로 0을 만들고 평균은 NULL 그대로 둔다(AVG는 NULL을 빼고 계산하므로 원래와 같다). 내부 조인으로 붙이면 그 상품이 상품 수에서 빠진다.

해설

인덱스로 푸는 안: reviews (product_id, rating) 인덱스를 만들면 스칼라 서브쿼리 한 번이 2 ~ 3블록이 되어 전체 약 1,700블록으로 준다. 하지만 상품마다 따로 찾는 구조는 그대로이고, 리뷰가 들어올 때마다 인덱스를 하나 더 고친다. 상품 전체를 다루는 요약표에는 한 번 묶는 쪽이 싸다.
인라인 뷰로 단계를 나누는 안: 스칼라 서브쿼리를 GROUP BY가 없는 인라인 뷰(상품 단위)로 옮기고 바깥에서 카테고리별로 묶으면 옵티마이저가 펼칠 수 있다. 다만 뷰가 병합되면 같은 모양으로 돌아가므로 계획에서 VW_SSQ가 나왔는지 확인해야 하고, 서브쿼리가 둘이면 리뷰를 두 번 읽을 수 있다.
평균의 평균: AVG(상품 평균)은 상품마다 같은 무게를 준다. 리뷰 전체의 평균(SUM(rating) / COUNT(*))과는 다른 숫자이므로 요구가 어느 쪽인지 화면 주인에게 확인해 둔다. 고친 SQL은 원래 뜻(평균의 평균)을 그대로 지켰다.
이 DB는 조인 키에 인덱스가 없다(교재 18화). 인덱스를 늘리기 전에 집합으로 한 번 읽는 방법부터 찾는다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황