「전 매장 주문 목록」 1쪽은 바로 뜨는데 400쪽은 한참 걸린다

기초SELECT버거324 / 700

상황

본사 운영팀의 「전 매장 주문 목록」은 200개 매장의 주문을 최신순(주문번호 역순)으로 한 쪽에 50건씩 보여 준다. 운영팀은 2주 전 마감분을 찾으려고 쪽 번호 칸에 400을 넣고 바로 넘어간다(하루 약 1,370건이라 400쪽이 2주 전쯤이다). 지난달 조민우가 예전 ROWNUM 페이징을 OFFSET … FETCH로 바꾸면서 이렇게 말했다. "OFFSET은 그 자리로 바로 건너뛰니까 400쪽도 1쪽처럼 빠르다." 아래는 1쪽과 400쪽을 열었을 때의 실행계획이다.

테이블과 인덱스

ORD 주문: 1,000,000행 · 22,422블록(블록당 약 45행) · ORD_NO NUMBER · ORD_DT VARCHAR2(8) · ORD_HMS VARCHAR2(6) · SHOP_NO
ORD_PK (ORD_NO): 높이 3 · 리프 약 2,000블록(리프 한 블록에 약 500개). 주문번호는 주문이 들어온 순서대로 매기고 테이블도 날짜순으로 쌓여 있어, ORD_PK 순서와 테이블 순서가 거의 같다(클러스터링 팩터 약 22,400).
ORD_X01 (ORD_DT, ORD_HMS)도 있다.
화면에 보이는 칸은 ORD_NO, ORD_DT, ORD_HMS, SHOP_NO 넷이다. 통계는 최신이다.

원본 SQL

sql

SELECT ORD_NO, ORD_DT, ORD_HMS, SHOP_NO
  FROM ORD
 ORDER BY ORD_NO DESC
 OFFSET :off ROWS FETCH NEXT 50 ROWS ONLY      -- :off = (쪽 번호 - 1) * 50

실행계획 학습용 예시

xplan

[1쪽 · :off = 0]
-----------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |        |      1 |        |     50 |00:00:00.01 |       5 |
|*  1 |  VIEW                          |        |      1 |     50 |     50 |00:00:00.01 |       5 |
|*  2 |   WINDOW NOSORT STOPKEY        |        |      1 |     50 |     50 |00:00:00.01 |       5 |
|   3 |    TABLE ACCESS BY INDEX ROWID | ORD    |      1 |   1000K|     50 |00:00:00.01 |       5 |
|   4 |     INDEX FULL SCAN DESCENDING | ORD_PK |      1 |     50 |     50 |00:00:00.01 |       3 |
-----------------------------------------------------------------------------------------------------

[400쪽 · :off = 19950]
-----------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |        |      1 |        |     50 |00:00:00.04 |     488 |
|*  1 |  VIEW                          |        |      1 |     50 |     50 |00:00:00.04 |     488 |
|*  2 |   WINDOW NOSORT STOPKEY        |        |      1 |     50 |  20000 |00:00:00.04 |     488 |
|   3 |    TABLE ACCESS BY INDEX ROWID | ORD    |      1 |   1000K|  20000 |00:00:00.03 |     488 |
|   4 |     INDEX FULL SCAN DESCENDING | ORD_PK |      1 |     50 |  20000 |00:00:00.01 |      42 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(("from$_subquery$_002"."rowlimit_$$_rownumber"<=CASE  WHEN (:OFF>=0) THEN
              FLOOR(TO_NUMBER(TO_CHAR(:OFF)))+50 ELSE 50 END  AND "from$_subquery$_002"."rowlimit_$$_rownumber">:OFF))
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("ORD_NO") DESC )<=CASE  WHEN (:OFF>=0) THEN
              FLOOR(TO_NUMBER(TO_CHAR(:OFF)))+50 ELSE 50 END )

문제

400쪽 실행계획의 2번 줄과 3번 줄에서 A-Rows가 20,000인 까닭을 쓰고, OFFSET이 "그 자리로 바로 건너뛴다"는 말이 맞는지 설명하시오.
몇 쪽째든 읽는 양이 비슷하도록 SQL을 고치시오. 고친 방식으로는 할 수 없게 되는 일이 있다면 그 대안도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 1쪽 : 맨 앞에서 51건 (50건 + '다음 쪽이 있나' 1건)
SELECT ORD_NO, ORD_DT, ORD_HMS, SHOP_NO
  FROM ORD
 ORDER BY ORD_NO DESC
 FETCH FIRST 51 ROWS ONLY;

-- 다음 쪽 : 앞 쪽 마지막 주문번호(:last_no)보다 작은 것부터 51건
SELECT ORD_NO, ORD_DT, ORD_HMS, SHOP_NO
  FROM ORD
 WHERE ORD_NO < :last_no
 ORDER BY ORD_NO DESC
 FETCH FIRST 51 ROWS ONLY;

-- 날짜로 가기 : 그날의 마지막 주문번호를 시작점으로 삼는다 (ORD_X01 로 그날 주문만 본다)
SELECT ORD_NO, ORD_DT, ORD_HMS, SHOP_NO
  FROM ORD
 WHERE ORD_NO <= (SELECT MAX(ORD_NO) FROM ORD WHERE ORD_DT = :d)
 ORDER BY ORD_NO DESC
 FETCH FIRST 51 ROWS ONLY;

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

xplan

[다음 쪽 · 몇 쪽째든 같은 모양]
------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |        |      1 |        |     51 |00:00:00.01 |       5 |
|*  1 |  VIEW                           |        |      1 |     51 |     51 |00:00:00.01 |       5 |
|*  2 |   WINDOW NOSORT STOPKEY         |        |      1 |     51 |     51 |00:00:00.01 |       5 |
|   3 |    TABLE ACCESS BY INDEX ROWID  | ORD    |      1 |  50000 |     51 |00:00:00.01 |       5 |
|*  4 |     INDEX RANGE SCAN DESCENDING | ORD_PK |      1 |     51 |     51 |00:00:00.01 |       3 |
------------------------------------------------------------------------------------------------------

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

모범 답안 서술

400쪽 실행계획의 2번 줄 WINDOW NOSORT STOPKEY와 3번 줄 테이블 액세스의 A-Rows는 20,000이다. OFFSET 19950 ROWS는 "19,950번째 다음"을 묻는데, 그 행이 어디 있는지는 인덱스를 맨 앞에서부터 세어 봐야 안다. 그래서 ORD_PK를 역순으로 20,000개 읽고(4번 줄 42블록), 행마다 테이블에 가서 화면 칸을 채운 다음(3번 줄까지 누적 488블록), 앞의 19,950건을 1번 줄에서 버린다. OFFSET은 문법만 바뀌었을 뿐 예전 ROWNUM 페이징과 같은 일을 하므로 "바로 건너뛴다"는 말은 틀렸다. 1쪽은 5블록, 400쪽은 488블록으로, 읽는 양이 쪽 번호에 비례해 늘어난다. 고친 SQL은 화면이 앞 쪽의 마지막 주문번호를 들고 있다가 ORD_NO < :last_no로 넘긴다. ORD_PK에서 그 번호 바로 아래를 찾아가(access) 역순으로 51건만 읽으므로 몇 쪽째든 약 5블록이다(400쪽 488 → 5). 51번째 행은 화면에 보이지 않고 「다음」 버튼을 켤지 정하는 데만 쓴다. 주문번호는 유일하므로 쪽 경계에서 빠지거나 겹치는 주문이 없고, 그사이에 새 주문이 들어와도 목록이 밀리지 않는다. 대신 400쪽으로 바로 가기는 할 수 없다. 운영팀이 400쪽으로 가는 까닭은 2주 전 마감분을 보려는 것이므로 '날짜로 가기'를 둔다. 그날의 마지막 주문번호를 시작점으로 삼으면 같은 모양으로 51건을 읽는다(시작점을 찾는 데 ORD_X01로 그날 주문 약 1,370건을 본다).

해설

ROWNUM으로 되돌려도 같다. ROWNUM <= 20000 … rn > 19950도 앞의 19,950건을 세며 지나간다. 비용을 만드는 것은 문법이 아니라 "몇 번째부터"라는 질문이다(교재 09화 §8).
이 테이블은 주문번호 순서와 쌓인 순서가 같아서 버리는 행 45개가 한 블록에 모여 있다. 그래서 400쪽이 488블록에 그쳤다. 클러스터링 팩터가 나쁜 테이블이었다면 버리는 행마다 다른 블록을 읽어 약 2만 블록이 된다(교재 09화: 같은 OFFSET이 클러스터링 팩터 하나로 44배 갈렸다).
쪽 번호를 꼭 지켜야 한다면 버릴 행은 인덱스의 ROWID만으로 세고 마지막 50건만 테이블에 가는 방법이 차선이다. 400쪽이면 인덱스 42블록과 테이블 2블록 정도다.
주문번호가 시간 순서라는 것은 채번 규칙에 기댄 전제다. 매장별로 번호 구간을 미리 나눠 주는 식으로 채번이 바뀌면 최신순이 깨지므로 그 전제를 대장에 적어 둔다.
:last_no는 SQL 문자열에 박지 말고 바인드 변수로 넘긴다. 박으면 쪽마다 다른 SQL이 되어 하드 파싱이 는다(교재 11화).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황