상품평 「더 보기」를 누르면 방금 본 평가가 또 나온다

기초SELECT한빛유통326 / 700

상황

상품 상세 화면 아래의 상품평은 최신순으로 10건을 보여 주고, 「더 보기」를 누르면 다음 10건을 이어 붙인다. 화면은 마지막으로 보인 평가의 평가일(EVL_DT)을 들고 있다가 다음 SQL에 넘긴다. 고객센터에 이런 문의가 들어왔다. "더 보기를 눌렀더니 방금 읽은 평가가 또 나온다. 어떤 상품은 아무리 눌러도 같은 평가만 나온다."

테이블과 인덱스

T_ITM_EVL 상품평가: 5,000행 · 48블록 · 상품당 50건 · PK_T_ITM_EVL (ITM_ID, EVL_LST_NO). 인덱스는 PK뿐이다.
EVL_LST_NO는 상품마다 평가가 등록된 순서대로 1부터 매기는 번호다.
EVL_DT는 DATE지만 앱이 TRUNC해서 넣어 날짜만 있다. 그래서 한 상품에 같은 날짜 평가가 여럿 있다(출시일에는 하루 12건인 상품도 있다).
상품 하나의 평가 50건은 한두 블록에 모여 있다. 통계는 최신이다.

원본 SQL

sql

SELECT EVL_LST_NO, CUS_ID, EVL_DT, EVL_PT
  FROM T_ITM_EVL
 WHERE ITM_ID = :itm
   AND EVL_DT <= :last_dt          -- 첫 화면은 :last_dt = 오늘
 ORDER BY EVL_DT DESC
 FETCH FIRST 10 ROWS ONLY

실행계획 학습용 예시

xplan

[「더 보기」 한 번 · 상품 하나 · :last_dt = 2024-06-14]
--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |              |      1 |        |     10 |00:00:00.01 |       4 |          |
|*  1 |  VIEW                                 |              |      1 |     10 |     10 |00:00:00.01 |       4 |          |
|*  2 |   WINDOW SORT PUSHED RANK             |              |      1 |      3 |     10 |00:00:00.01 |       4 | 2048 (0) |
|*  3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T_ITM_EVL    |      1 |      3 |     43 |00:00:00.01 |       4 |          |
|*  4 |     INDEX RANGE SCAN                  | PK_T_ITM_EVL |      1 |     50 |     50 |00:00:00.01 |       2 |          |
--------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=10)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("EVL_DT") DESC )<=10)
   3 - filter("EVL_DT"<=:LAST_DT)
   4 - access("ITM_ID"=:ITM)

[화면 · 평가 번호 (평가일)]
  첫 화면   50 49 48 47 46 45 44 · 43 41 42      (43 · 42 · 41 은 2024-06-14, 마지막 줄은 42)
  더 보기   41 43 42 · 40 39 38 37 36 35 34      ← 6월 14일 평가 세 건이 다시 나온다

문제

「더 보기」 뒤에 6월 14일 평가 세 건이 다시 나온 까닭을 정렬 키와 조건으로 설명하시오. 같은 날짜 평가가 10건을 넘는 상품에서 "아무리 눌러도 같은 평가만 나오는" 까닭도 쓰시오.
평가가 빠지거나 겹치지 않도록 SQL을 고치시오. 조건을 EVL_DT < :last_dt로만 바꾸면 어떻게 되는지도 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 평가 번호는 상품마다 등록 순서대로 매긴다 (번호 순서와 날짜 순서가 어긋나는 쌍 0건을 아래에서 확인)
-- 첫 화면 : 맨 앞에서 10건
SELECT EVL_LST_NO, CUS_ID, EVL_DT, EVL_PT
  FROM T_ITM_EVL
 WHERE ITM_ID = :itm
 ORDER BY EVL_LST_NO DESC
 FETCH FIRST 10 ROWS ONLY;

-- 더 보기 : 마지막으로 보인 평가 번호(:last_no) 다음부터
SELECT EVL_LST_NO, CUS_ID, EVL_DT, EVL_PT
  FROM T_ITM_EVL
 WHERE ITM_ID = :itm
   AND EVL_LST_NO < :last_no
 ORDER BY EVL_LST_NO DESC
 FETCH FIRST 10 ROWS ONLY;

-- 전제 확인 : 번호 순서와 날짜 순서가 어긋나는 쌍 (0 이어야 한다)
SELECT COUNT(*)
  FROM (SELECT EVL_DT,
               LAG(EVL_DT) OVER (PARTITION BY ITM_ID ORDER BY EVL_LST_NO) AS prev_dt
          FROM T_ITM_EVL)
 WHERE EVL_DT < prev_dt;

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

xplan

[더 보기 · 정렬 줄이 없다]
------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |              |      1 |        |     10 |00:00:00.01 |       3 |
|*  1 |  VIEW                           |              |      1 |     10 |     10 |00:00:00.01 |       3 |
|*  2 |   WINDOW NOSORT STOPKEY         |              |      1 |     10 |     10 |00:00:00.01 |       3 |
|   3 |    TABLE ACCESS BY INDEX ROWID  | T_ITM_EVL    |      1 |      3 |     10 |00:00:00.01 |       3 |
|*  4 |     INDEX RANGE SCAN DESCENDING | PK_T_ITM_EVL |      1 |      3 |     10 |00:00:00.01 |       2 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=10)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("EVL_LST_NO") DESC )<=10)
   4 - access("ITM_ID"=:ITM AND "EVL_LST_NO"<:LAST_NO)

모범 답안 서술

첫 화면의 마지막 줄(평가 42)은 6월 14일이고, 「더 보기」는 EVL_DT <= 6월 14일로 다음 10건을 찾는다. 6월 14일 평가 세 건(43, 42, 41)은 첫 화면에 이미 나왔는데 이 조건에 다시 걸리므로 「더 보기」 맨 앞에 또 나온다. 평가일은 날짜만 있어 같은 날 평가가 여럿이고, 정렬 키 EVL_DT 하나로는 같은 날 평가끼리의 순서도 어디까지 봤는지도 정해지지 않는다. 같은 날 평가가 10건을 넘는 상품은 「더 보기」가 매번 그날 평가 10건을 다시 가져오므로 다음으로 넘어가지 못한다. 조건을 EVL_DT < :last_dt로만 바꾸면 반대로 같은 날의 남은 평가를 건너뛴다. 6월 14일에 평가가 다섯 건이고 첫 화면에 세 건만 나왔다면 나머지 두 건은 어디에도 나오지 않는다(겹침 대신 누락). 평가 번호 EVL_LST_NO는 상품 안에서 유일하고 등록 순서대로 매긴다. 번호 순서와 날짜 순서가 어긋나는 쌍이 0건인지 LAG로 먼저 확인하고, 정렬 키를 평가 번호 하나로 바꿔 마지막으로 본 번호보다 작은 것부터 10건을 읽는다. 정렬 키가 유일하므로 겹치거나 빠지는 평가가 없다. PK 인덱스 (ITM_ID, EVL_LST_NO)가 이미 그 순서라서 정렬 줄(WINDOW SORT PUSHED RANK)도 사라지고, 인덱스를 거꾸로 읽다가 10건에서 멈춘다(Buffers 4 → 3).

해설

평가일을 정렬 키에 남기는 다른 답: 정렬 키를 EVL_DT DESC, EVL_LST_NO DESC로 하고 조건을 EVL_DT <= :d AND (EVL_DT < :d OR EVL_LST_NO < :no)로 풀어 써도 맞다. 번호와 날짜 순서가 어긋날 수 있는 테이블(예전 평가를 나중에 옮겨 넣는 경우)이라면 이쪽을 쓴다. 다만 PK가 (ITM_ID, EVL_LST_NO)라서 정렬 줄이 남는다. 상품당 50건이라 비용은 작다.
평가 번호 하나로 정렬하는 답은 "번호를 등록 순서대로 매긴다"는 채번 규칙에 기댄 판단이다. 교재 09화가 ora-dist의 판매번호를 정렬 키로 삼을 때처럼, 전제를 재서 대장에 적고 채번 방식이 바뀌면 다시 잰다.
이 문제는 성능이 아니라 정답의 문제다. 블록은 4에서 3으로 줄 뿐이고, 얻는 것은 빠지거나 겹치지 않는 목록이다.
TRUNC로 시각을 버린 것이 같은 날 평가를 만들었다. 시각까지 저장해도 같은 초의 평가가 없다는 보장은 없으므로 유일한 값으로 끝나는 정렬 키가 여전히 필요하다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황