상품 주문 내역이 1쪽만 나오고 2쪽부터는 빈 화면인데, 빈 화면이 1쪽보다 늦게 뜬다

중급SELECT한빛유통316 / 700

상황

상품관리팀의 「상품 주문 내역」은 상품 하나의 주문 줄을 최근 주문부터 한 쪽에 10줄씩 보여 준다. 지난달 이 화면을 회사 공통 페이징 함수로 옮긴 뒤로 "1쪽은 나오는데 2쪽을 누르면 빈 화면이 뜬다"는 말이 나왔다. 조민우가 재 보니 빈 화면이 1쪽보다 오히려 오래 걸린다. 공통 페이징 함수는 원래 SQL을 인라인 뷰로 감싸고 바깥에 WHERE ROWNUM BETWEEN :lo AND :hi를 붙인다. 1쪽은 1 ~ 10, 2쪽은 11 ~ 20이다. 상품관리팀과 고객센터가 하루 약 6천 번 연다.

테이블과 인덱스

T_ORD_DET 주문상세: 200,000행 · 896블록 · ORD_SEQ · ORD_DET_NO · ITM_ID · ORD_QTY · UNT_PRC · PK_T_ORD_DET (ORD_SEQ, ORD_DET_NO) · 상품 100개, 상품마다 약 2,000줄
주문번호 순으로 쌓여 있어 상품 하나의 줄은 테이블 곳곳의 블록에 흩어져 있다. 주문번호는 주문이 들어온 순서대로 매긴다.
인덱스 IX_T_ORD_DET_ITM (ITM_ID, ORD_SEQ, ORD_DET_NO): 이 화면용 · BLEVEL 1 · 리프 약 560블록 · 아래 계획은 주문 줄이 1,987줄인 상품이다. 통계는 최신이다.

원본 SQL

sql

SELECT ORD_SEQ, ORD_DET_NO, ORD_QTY, UNT_PRC
  FROM (SELECT ORD_SEQ, ORD_DET_NO, ORD_QTY, UNT_PRC
          FROM T_ORD_DET
         WHERE ITM_ID = :itm
         ORDER BY ORD_SEQ DESC, ORD_DET_NO DESC)
 WHERE ROWNUM BETWEEN :lo AND :hi      -- 공통 페이징 함수가 붙인다 (2쪽 = 11 ~ 20)

실행계획 학습용 예시 · 계획의 모양(COUNT STOPKEY 아래 FILTER)은 교재 환경 23ai에서 확인

xplan

[1쪽 · :lo = 1, :hi = 10]
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                  |      1 |        |     10 |00:00:00.01 |      12 |
|*  1 |  COUNT STOPKEY                    |                  |      1 |        |     10 |00:00:00.01 |      12 |
|*  2 |   FILTER                          |                  |      1 |        |     10 |00:00:00.01 |      12 |
|   3 |    VIEW                           |                  |      1 |     10 |     10 |00:00:00.01 |      12 |
|   4 |     TABLE ACCESS BY INDEX ROWID   | T_ORD_DET        |      1 |   2000 |     10 |00:00:00.01 |      12 |
|*  5 |      INDEX RANGE SCAN DESCENDING  | IX_T_ORD_DET_ITM |      1 |     10 |     10 |00:00:00.01 |       2 |
----------------------------------------------------------------------------------------------------------------

[2쪽 · :lo = 11, :hi = 20]
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                  |      1 |        |      0 |00:00:00.02 |     804 |
|*  1 |  COUNT STOPKEY                    |                  |      1 |        |      0 |00:00:00.02 |     804 |
|*  2 |   FILTER                          |                  |      1 |        |      0 |00:00:00.02 |     804 |
|   3 |    VIEW                           |                  |      1 |     20 |   1987 |00:00:00.02 |     804 |
|   4 |     TABLE ACCESS BY INDEX ROWID   | T_ORD_DET        |      1 |   2000 |   1987 |00:00:00.02 |     804 |
|*  5 |      INDEX RANGE SCAN DESCENDING  | IX_T_ORD_DET_ITM |      1 |     20 |   1987 |00:00:00.01 |       7 |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=:HI)
   2 - filter(ROWNUM>=:LO)
   5 - access("ITM_ID"=:ITM)

문제

2쪽이 언제나 0건인 까닭을 ROWNUM이 매겨지는 방식과 2번 줄 FILTER로 설명하시오.
0건을 돌려주는 2쪽이 10건을 돌려주는 1쪽보다 더 많이 읽는 까닭을 1번 줄 COUNT STOPKEY와 3번 줄 A-Rows로 설명하시오.
몇 쪽이든 그 쪽의 10줄이 나오고, 그 쪽까지만 읽고 멈추도록 SQL을 고치시오.

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

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

모범답안

모범 답안 SQL

sql

-- 번호는 안쪽에서 매겨 컬럼(rn)으로 만들고, 바깥에서 그 번호로 거른다
-- 안쪽의 ROWNUM <= :hi 는 남긴다 : COUNT STOPKEY 가 그 쪽의 끝(:hi)에서 멈춘다
SELECT ORD_SEQ, ORD_DET_NO, ORD_QTY, UNT_PRC
  FROM (SELECT ROWNUM AS rn, a.*
          FROM (SELECT ORD_SEQ, ORD_DET_NO, ORD_QTY, UNT_PRC
                  FROM T_ORD_DET
                 WHERE ITM_ID = :itm
                 ORDER BY ORD_SEQ DESC, ORD_DET_NO DESC) a
         WHERE ROWNUM <= :hi)
 WHERE rn >= :lo

개선 후 실행계획 학습용 예시 · 계획의 모양(COUNT STOPKEY 아래 FILTER)은 교재 환경 23ai에서 확인

xplan

[2쪽 · :lo = 11, :hi = 20]
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                  |      1 |        |     10 |00:00:00.01 |      22 |
|*  1 |  VIEW                             |                  |      1 |     20 |     10 |00:00:00.01 |      22 |
|*  2 |   COUNT STOPKEY                   |                  |      1 |        |     20 |00:00:00.01 |      22 |
|   3 |    VIEW                           |                  |      1 |     20 |     20 |00:00:00.01 |      22 |
|   4 |     TABLE ACCESS BY INDEX ROWID   | T_ORD_DET        |      1 |   2000 |     20 |00:00:00.01 |      22 |
|*  5 |      INDEX RANGE SCAN DESCENDING  | IX_T_ORD_DET_ITM |      1 |     20 |     20 |00:00:00.01 |       2 |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("RN">=:LO)
   2 - filter(ROWNUM<=:HI)
   5 - access("ITM_ID"=:ITM)

모범 답안 서술

ROWNUM은 행이 조건을 통과할 때 매겨지는 번호라서, 첫 행은 무조건 1번을 받는다. 2쪽에서는 그 1번이 2번 줄 FILTER의 ROWNUM >= 11을 통과하지 못해 버려지고, 번호가 올라가지 않으니 다음 행도 다시 1번을 받아 버려진다. 11번까지 가는 행이 하나도 없으므로 결과는 언제나 0건이다. 1쪽(1 ~ 10)만 나온 것은 ROWNUM >= 1이 늘 참이기 때문이다. 빈 화면이 1쪽보다 늦은 까닭은 1번 줄 COUNT STOPKEY가 멈추지 못해서다. STOPKEY는 통과한 행이 :hi(20)개가 되어야 멈추는데 통과하는 행이 없으므로, 그 상품의 주문 줄 1,987줄을 인덱스로 끝까지 찾아 테이블을 다 읽는다(3번 줄 A-Rows 1,987, Buffers 804). 상품 하나의 줄이 테이블 곳곳에 흩어져 있어 896블록 가운데 약 800블록을 오간다. 10줄에서 멈춘 1쪽은 12블록이다. 고친 SQL은 번호를 안쪽 인라인 뷰에서 ROWNUM AS rn으로 매겨 컬럼으로 만들고, 바깥에서 rn >= :lo로 거른다. 안쪽에는 ROWNUM <= :hi를 그대로 남겨야 2번 줄 COUNT STOPKEY가 20번째 행에서 멈춘다. 2쪽은 20행을 읽고 앞의 10행을 버려 Buffers 804 → 22다. 번호를 바깥에서만 거르면(rn BETWEEN :lo AND :hi) 결과는 맞지만 안쪽에 상한이 없어 STOPKEY가 사라지고(COUNT), 쪽과 상관없이 1,987줄을 끝까지 읽는다. 고친 SQL도 쪽 번호가 커질수록 앞에서 버리는 줄이 늘어 200쪽째는 2,000줄을 읽는다. 대부분 앞쪽 몇 쪽만 보는 화면이라 이 방식으로 충분하고, 깊은 쪽을 자주 본다면 키 기반 페이징으로 바꾼다(SORT-001).

해설

ROWNUM > n은 늘 0건이다: ROWNUM = 1, ROWNUM <= n처럼 1번부터 이어지는 조건만 뜻이 있다. ROWNUM > 10이나 ROWNUM = 11은 문법 오류가 아니라서 조용히 빈 결과를 낸다. 공통 함수가 만든 SQL을 V$SQL에서 ROWNUM BETWEEN으로 찾아 같은 틀을 쓰는 화면을 함께 고친다.
12c 이후의 다른 답: ORDER BY … OFFSET :lo - 1 ROWS FETCH NEXT 10 ROWS ONLY도 결과가 같다. 앞의 행을 읽고 버리는 것도 같아서 읽는 양은 고친 SQL과 비슷하다(교재 09화 §8).
ROW_NUMBER() OVER (ORDER BY ORD_SEQ DESC, ORD_DET_NO DESC)로 번호를 매기고 바깥에서 BETWEEN으로 걸러도 맞다. 바깥 조건에 상한이 있으면 WINDOW NOSORT STOPKEY로 멈춘다.
정렬 키 (ORD_SEQ, ORD_DET_NO)가 PK라서 유일하다. 정렬 키가 유일하지 않으면 쪽 경계에서 줄이 빠지거나 겹친다(SORT-021).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 계획의 모양(COUNT STOPKEY 아래 FILTER)은 교재 환경 23ai에서 확인 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 계획의 모양(COUNT STOPKEY 아래 FILTER)은 교재 환경 23ai에서 확인 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황