상품을 골라도 리뷰 관리 화면이 리뷰 1만 5천 건을 다 읽는다

기초SELECT이커머스51 / 700

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

상황

이커머스 운영팀의 「리뷰 관리」 화면은 상품을 고르면 그 상품의 리뷰를, 고르지 않으면 전체 리뷰를 최신순으로 보여 준다. 운영자 열두 명이 하루 약 2천 번 열고, 대부분은 상품을 고른다. 이 화면의 조건절은 공통 조회 모듈이 만든다. 모듈은 옵션 조건마다 "NULL이 와도 안전하게" 컬럼과 바인드 양쪽에 NVL을 씌운다. 아래는 상품 하나(:p_id = 57, 리뷰 54건)를 골랐을 때다.

테이블과 인덱스

reviews: 15,000행 · 775블록 · review_id PK · product_id(상품 280종, 상품당 34 ~ 76건, NULL 0건이지만 NOT NULL 제약은 없다) · customer_id · rating · content CLOB(화면은 앞 100자만 보여 준다) · created_at
인덱스 IX_REVIEWS_PRD (product_id): 이 문제를 위해 만들어 둔 인덱스 · BLEVEL 1 · 통계 최신
한 상품의 리뷰는 테이블 곳곳에 흩어져 있다

원본 SQL

sql

SELECT review_id, product_id, customer_id, rating, created_at,
       DBMS_LOB.SUBSTR(content, 100, 1) AS content_head
  FROM reviews
 WHERE NVL(product_id, -1) = NVL(:p_id, NVL(product_id, -1))
 ORDER BY created_at DESC

실행계획 학습용 예시

xplan

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

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(NVL("PRODUCT_ID",(-1))=NVL(:P_ID,NVL("PRODUCT_ID",(-1))))

문제

상품을 골랐는데도 IX_REVIEWS_PRD를 쓰지 못하는 이유를 Predicate Information으로 설명하시오.
상품을 고르면 인덱스로, 고르지 않으면 전체를 읽도록 조건절을 고치시오. 고친 조건이 원래 조건과 같은 결과를 내는지 product_id가 NULL인 행의 관점에서 따져 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

SELECT review_id, product_id, customer_id, rating, created_at,
       DBMS_LOB.SUBSTR(content, 100, 1) AS content_head
  FROM reviews
 WHERE (:p_id IS NULL OR product_id = :p_id)     -- 컬럼은 그대로, 옵션 처리는 바인드 쪽에서
 ORDER BY created_at DESC

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

xplan

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name            | Starts | E-Rows | A-Rows | A-Time      | Buffers |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                 |      1 |        |     54 | 00:00:00.01 |      56 |
|   1 |  SORT ORDER BY                           |                 |      1 |  15054 |     54 | 00:00:00.01 |      56 |
|   2 |   VIEW                                   | VW_ORE_3D9B7C21 |      1 |  15054 |     54 | 00:00:00.01 |      56 |
|   3 |    UNION-ALL                             |                 |      1 |        |     54 | 00:00:00.01 |      56 |
|*  4 |     FILTER                               |                 |      1 |        |      0 | 00:00:00.01 |       0 |
|   5 |      TABLE ACCESS FULL                   | REVIEWS         |      0 |  15000 |      0 | 00:00:00.01 |       0 |
|*  6 |     FILTER                               |                 |      1 |        |     54 | 00:00:00.01 |      56 |
|   7 |      TABLE ACCESS BY INDEX ROWID BATCHED | REVIEWS         |      1 |     54 |     54 | 00:00:00.01 |      56 |
|*  8 |       INDEX RANGE SCAN                   | IX_REVIEWS_PRD  |      1 |     54 |     54 | 00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter(:P_ID IS NULL)
   6 - filter(LNNVL(:P_ID IS NULL))
   8 - access("PRODUCT_ID"=:P_ID)

모범 답안 서술

2번 줄의 조건은 filter(NVL("PRODUCT_ID",(-1))=...)이다. IX_REVIEWS_PRD에는 product_id 값이 그대로 정렬되어 있는데, 조건의 왼쪽은 컬럼을 NVL로 감싼 값이라 그 정렬 위에서 범위를 정할 수 없다. 그래서 상품을 골라도 테이블 1만 5천 행을 다 읽고 54건을 남긴다(Buffers 775). NVL은 컬럼 쪽이 아니라 바인드 쪽에서만 쓴다. "값이 없으면 전부, 있으면 그 값"은 (:p_id IS NULL OR product_id = :p_id)로 쓴다. 컬럼이 가공되지 않았으므로 옵티마이저가 OR-Expansion으로 두 갈래를 만든다. 4번 줄의 :P_ID IS NULL 갈래는 FULL이고, 6번 줄의 LNNVL(:P_ID IS NULL) 갈래는 IX_REVIEWS_PRD로 찾아간다. 어느 갈래를 탈지는 실행할 때 바인드 값이 정한다. 상품을 고르면 FULL 갈래는 Starts 0으로 건너뛰고 인덱스 갈래만 돈다(Buffers 775 → 56). 상품을 고르지 않으면 FULL 갈래만 돈다. 커서는 하나다. 고친 조건은 원래 조건과 결과가 같다. 원래 조건은 :p_id가 NULL이면 양쪽이 모두 NVL(product_id, -1)이 되어 product_id가 NULL인 리뷰까지 전부 돌려준다. (:p_id IS NULL OR ...)도 :p_id가 NULL이면 조건이 참이라 NULL인 리뷰까지 전부 돌려준다. 흔히 쓰는 product_id = NVL(:p_id, product_id)는 :p_id가 NULL일 때 product_id가 NULL인 행을 버리므로 이 자리에 맞지 않는다(IDX-058).

해설

공통 모듈이 만든 조건이므로 다른 화면의 SQL에도 같은 모양이 있다. 모듈 한 곳을 고치면 모두 바뀌므로, 그 화면들의 계획도 함께 확인한다.
상품을 고르지 않는 경우는 여전히 FULL이다. 전체 리뷰를 최신순으로 보는 요구라면 created_at 인덱스와 첫 페이지만 읽는 페이징으로 따로 푼다.
OR-Expansion은 옵션 조건이 하나일 때 잘 듣는다. 옵션 조건이 여럿이면 펼치지 않거나 하나만 펼치므로, 갈래를 손으로 나누거나 동적 SQL을 쓴다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황