패치 뒤로 상품 상세의 「최신 리뷰」가 날짜순이 아니라 뒤죽박죽으로 나온다

기초SELECT이커머스282 / 700

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

상황

상품 상세 화면의 「최신 리뷰」 칸은 그 상품의 리뷰를 작성일 역순으로 보여 주고 작성자 이름을 붙인다. 지난주 DB 패치와 통계 재수집 뒤로 "리뷰가 날짜순이 아니라 뒤죽박죽으로 나온다"는 고객 문의가 들어왔다. 개발자는 "인라인 뷰 안에 ORDER BY를 분명히 넣었고, 패치 전에는 잘 나왔다"라고 한다. 패치 전 계획에는 VIEW와 SORT ORDER BY 줄이 있었고, 고객 테이블은 PK로 NL 조인했다.

테이블과 인덱스

reviews: 15,000행 · 775블록 · review_id · product_id · customer_id · rating · content CLOB · created_at · 상품당 34 ~ 76건 · PK 말고는 인덱스가 없다
customers: 5,230행 · 51블록 · customer_id PK
아래 실행계획은 리뷰 53건인 상품 · 통계 최신

원본 SQL

sql

SELECT r.review_id, r.rating, r.created_at, c.name
  FROM (SELECT review_id, customer_id, rating, created_at
          FROM reviews
         WHERE product_id = :pid
         ORDER BY created_at DESC) r,
       customers c
 WHERE c.customer_id = r.customer_id

실행계획 학습용 예시

xplan

-------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |      1 |        |     53 |00:00:00.01 |     826 |          |
|*  1 |  HASH JOIN         |           |      1 |     53 |     53 |00:00:00.01 |     826 | 1236K (0)|
|*  2 |   TABLE ACCESS FULL| REVIEWS   |      1 |     53 |     53 |00:00:00.01 |     775 |          |
|   3 |   TABLE ACCESS FULL| CUSTOMERS |      1 |   5230 |   5230 |00:00:00.01 |      51 |          |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("C"."CUSTOMER_ID"="CUSTOMER_ID")
   2 - filter("PRODUCT_ID"=:PID)

[화면 맨 위 다섯 줄의 created_at]
  2023-03-11 · 2024-08-02 · 2021-12-19 · 2024-11-30 · 2022-06-07

문제

지금 실행계획에 SORT ORDER BY와 VIEW 줄이 없는 이유를 설명하시오. 패치 전에는 순서가 맞게 나온 이유도 설명하시오.
리뷰가 언제나 작성일 역순으로 나오도록 SQL을 고치고, 그 정렬의 비용을 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 결과 순서는 맨 바깥 ORDER BY만 약속한다
SELECT r.review_id, r.rating, r.created_at, c.name
  FROM reviews r, customers c
 WHERE r.product_id = :pid
   AND c.customer_id = r.customer_id
 ORDER BY r.created_at DESC

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

xplan

--------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |      1 |        |     53 |00:00:00.01 |     826 |          |
|   1 |  SORT ORDER BY      |           |      1 |     53 |     53 |00:00:00.01 |     826 | 6144  (0)|
|*  2 |   HASH JOIN         |           |      1 |     53 |     53 |00:00:00.01 |     826 | 1236K (0)|
|*  3 |    TABLE ACCESS FULL| REVIEWS   |      1 |     53 |     53 |00:00:00.01 |     775 |          |
|   4 |    TABLE ACCESS FULL| CUSTOMERS |      1 |   5230 |   5230 |00:00:00.01 |      51 |          |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C"."CUSTOMER_ID"="R"."CUSTOMER_ID")
   3 - filter("R"."PRODUCT_ID"=:PID)

모범 답안 서술

지금 계획에는 VIEW 줄도 SORT ORDER BY 줄도 없다. 인라인 뷰가 조건만 있는 단순한 뷰라서 옵티마이저가 바깥 쿼리에 합쳤고(뷰 머징), 그러면서 뷰 안의 ORDER BY를 지웠다. 합쳐진 뒤에는 뷰 안의 정렬이 최종 결과의 순서를 정하지 않으므로, 옵티마이저 입장에서 그 정렬은 쓸데없는 일이다(ORDER BY 제거). SQL에서 결과 순서를 보장하는 것은 맨 바깥 쿼리의 ORDER BY뿐이다. 인라인 뷰 안의 ORDER BY는 조인 뒤의 순서를 약속하지 않는다. 지금 계획은 해시 조인이라 리뷰 53건으로 해시 테이블을 만들고 고객 5,230명을 훑으며 짝을 찾는다. 결과는 프로브 쪽인 고객 테이블을 읽는 순서로 나오므로 날짜 순서가 흩어진다. 패치 전에는 뷰가 합쳐지지 않아 리뷰를 먼저 정렬했고, 고객을 PK로 찾는 NL 조인이 그 순서를 그대로 따라 내보냈다. 순서가 맞았던 것은 그 계획의 우연이지 SQL의 약속이 아니었다. 통계가 바뀌어 해시 조인이 되자 우연이 깨졌다. ORDER BY를 맨 바깥으로 옮긴다. 정렬할 행은 53건뿐이라 비용은 거의 없고(6KB), Buffers는 826 그대로다. 이제 계획이 NL이든 해시든 순서는 바뀌지 않는다.

해설

같은 습관, 다른 결과: Top-N 쿼리(SELECT * FROM (… ORDER BY …) WHERE ROWNUM <= 10)에서는 뷰 안의 ORDER BY가 꼭 필요하다. ROWNUM이 있으면 뷰를 합치지 않고 정렬도 지우지 않는다. 조인만 하는 뷰에서는 정렬이 지워질 수 있다.
패치 전 계획으로 되돌리기: NO_MERGE를 붙여 뷰를 따로 두면 당장은 다시 맞게 나온다. 하지만 해시 조인이 되면 같은 일이 벌어질 수 있으므로 답이 아니다. 순서는 힌트가 아니라 ORDER BY로 약속한다.
남은 일량: 775블록은 reviews를 통째로 읽는 몫이다. 이 테이블의 조인 키와 조회 키에 인덱스가 없는 이유와 대책은 교재 18화 졸업 과제에서 다룬다.
결과가 맞는지는 Buffers로 알 수 없다. 화면 순서처럼 눈에 보이는 결과는 정합성 점검 목록에 따로 넣어 확인한다(교재 08화 정합성 회귀 체크리스트).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황