「메뉴별 판매 내역」은 앞쪽은 바로 뜨는데 200쪽쯤부터 1초 가까이 걸린다

중급SELECT버거333 / 700

상황

본사 메뉴팀의 「메뉴별 판매 내역」은 메뉴 하나의 주문상품 줄을 최신순으로 한 쪽에 50줄씩 보여 준다. 메뉴팀은 그 메뉴가 몇 달 전에 어땠는지 보려고 쪽 번호를 200, 300처럼 넣고 바로 넘어간다. 쪽 번호와 화면 모양은 다음 분기 개편 때까지 바꾸지 않기로 했다. 이번 분기에는 ORD_ITEM에 인덱스를 더 만들거나 키우는 작업도 승인되지 않는다(주문상품 적재 배치의 부담 때문이다). 아래는 메뉴 하나의 200쪽(9,951 ~ 10,000번째 줄)을 열었을 때의 실행계획이다.

테이블과 인덱스

ORD_ITEM 주문상품: 2,472,717행 · 55,265블록 · ORD_NO · ITEM_ID · ORD_ITEM_QTY · ORD_DT · ORD_HMS · 메뉴 92개 · 메뉴 하나에 평균 약 2만 7천 줄
인덱스 ORD_ITEM_X03 (ITEM_ID, ORD_DT, ORD_HMS, ORD_NO): 지난 분기 이 화면용으로 만들었다. 리프 한 블록에 약 210개 항목이 들어간다. 한 메뉴의 줄은 테이블 전체에 흩어져 있어 줄마다 다른 블록에 있다(클러스터링 팩터가 행 수에 가깝다).
ORD_ITEM_PK (ORD_NO, ITEM_ID)이므로 메뉴 하나 안에서는 ORD_NO가 유일하다. 오래된 블록은 대부분 버퍼 캐시에 없다. 통계는 최신이다.

원본 SQL

sql

SELECT ORD_NO, ORD_DT, ORD_HMS, ORD_ITEM_QTY
  FROM (SELECT ROWNUM rn, a.*
          FROM (SELECT ORD_NO, ORD_DT, ORD_HMS, ORD_ITEM_QTY
                  FROM ORD_ITEM
                 WHERE ITEM_ID = :item
                 ORDER BY ORD_DT DESC, ORD_HMS DESC, ORD_NO DESC) a
         WHERE ROWNUM <= :page * 50)
 WHERE rn > (:page - 1) * 50

실행계획 학습용 예시

xplan

[200쪽 · :page = 200]   (1쪽은 같은 모양 · A-Rows 50 · Buffers 53 · 0.01초)
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |              |      1 |        |     50 |00:00:00.94 |   10041 |  8873 |
|*  1 |  VIEW                           |              |      1 |  10000 |     50 |00:00:00.94 |   10041 |  8873 |
|*  2 |   COUNT STOPKEY                 |              |      1 |        |  10000 |00:00:00.94 |   10041 |  8873 |
|   3 |    VIEW                         |              |      1 |  26877 |  10000 |00:00:00.93 |   10041 |  8873 |
|   4 |     TABLE ACCESS BY INDEX ROWID | ORD_ITEM     |      1 |  26877 |  10000 |00:00:00.93 |   10041 |  8873 |
|*  5 |      INDEX RANGE SCAN DESCENDING| ORD_ITEM_X03 |      1 |  26877 |  10000 |00:00:00.01 |      51 |     0 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("RN">(:PAGE-1)*50)
   2 - filter(ROWNUM<=:PAGE*50)
   5 - access("ITEM_ID"=:ITEM)

문제

200쪽에서 4번 줄이 테이블을 10,000번 찾아가는 까닭과, 그 가운데 화면에 쓰이는 것이 몇 번인지 설명하시오.
쪽 번호와 인덱스는 그대로 두고, 버릴 줄은 테이블을 읽지 않도록 SQL을 고치시오.
고친 SQL이 의도대로 돌려면 실행계획에서 무엇을 확인하고 무엇을 고정해야 하는지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 버릴 9,950줄은 인덱스 항목(ROWID)만으로 세고, 쪽 분량 50줄만 테이블에 간다
SELECT /*+ LEADING(x) USE_NL(t) */
       t.ORD_NO, t.ORD_DT, t.ORD_HMS, t.ORD_ITEM_QTY
  FROM (SELECT rid, rn
          FROM (SELECT ROWNUM rn, rid
                  FROM (SELECT ROWID rid
                          FROM ORD_ITEM
                         WHERE ITEM_ID = :item
                         ORDER BY ORD_DT DESC, ORD_HMS DESC, ORD_NO DESC)
                 WHERE ROWNUM <= :page * 50)
         WHERE rn > (:page - 1) * 50) x,
       ORD_ITEM t
 WHERE t.ROWID = x.rid
 ORDER BY x.rn

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

xplan

[200쪽]
-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |              |      1 |        |     50 |00:00:00.02 |     101 |    46 |          |
|   1 |  SORT ORDER BY                    |              |      1 |     50 |     50 |00:00:00.02 |     101 |    46 | 4096 (0) |
|   2 |   NESTED LOOPS                    |              |      1 |     50 |     50 |00:00:00.02 |     101 |    46 |          |
|*  3 |    VIEW                           |              |      1 |  10000 |     50 |00:00:00.01 |      51 |     0 |          |
|*  4 |     COUNT STOPKEY                 |              |      1 |        |  10000 |00:00:00.01 |      51 |     0 |          |
|   5 |      VIEW                         |              |      1 |  26877 |  10000 |00:00:00.01 |      51 |     0 |          |
|*  6 |       INDEX RANGE SCAN DESCENDING | ORD_ITEM_X03 |      1 |  26877 |  10000 |00:00:00.01 |      51 |     0 |          |
|   7 |    TABLE ACCESS BY USER ROWID     | ORD_ITEM     |     50 |      1 |     50 |00:00:00.01 |      50 |    46 |          |
-----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("RN">(:PAGE-1)*50)
   4 - filter(ROWNUM<=:PAGE*50)
   6 - access("ITEM_ID"=:ITEM)

모범 답안 서술

4번 줄 TABLE ACCESS BY INDEX ROWID는 Starts가 1이지만 A-Rows가 10,000이다. 화면 칸 ORD_ITEM_QTY가 인덱스에 없어서, 인라인 뷰가 줄마다 테이블에 가서 칸을 채운 다음에야 2번 줄 ROWNUM이 줄 수를 센다. 200쪽은 9,951 ~ 10,000번째 줄이므로 테이블을 10,000번 찾아가 그중 9,950번은 1번 줄에서 버리고, 화면에 쓰이는 것은 50번이다. 한 메뉴의 줄이 테이블 곳곳에 흩어져 있어 찾아갈 때마다 다른 블록이고, 오래된 블록이라 대부분 디스크에서 읽는다(Buffers 10,041, Reads 8,873, 0.94초). 쪽 번호에 비례해 늘어나므로 400쪽이면 두 배다. 버릴 줄을 세는 데는 테이블의 칸이 필요 없다. 메뉴 조건과 정렬 키가 모두 ORD_ITEM_X03에 있으므로 안쪽 인라인 뷰는 인덱스에서 ROWID만 골라 순서대로 세고, 쪽 분량 50줄의 ROWID만 남긴다. 그 50개로 테이블을 찾아가 칸을 채우고(TABLE ACCESS BY USER ROWID 50번), 마지막에 ORDER BY x.rn으로 순서를 다시 세운다. Buffers는 10,041 → 101(인덱스 51, 테이블 50)이고 인덱스는 그대로다. 이 SQL은 ROWID로 50번 찾아가는 NL 조인일 때만 싸다. 옵티마이저가 쪽이 깊을수록 늘어나는 rid 건수 추정을 보고 해시 조인과 ORD_ITEM FULL(55,265블록)을 고를 수 있으므로, LEADING(x) USE_NL(t)로 조인 순서와 방법을 고정하고 실행계획에 TABLE ACCESS BY USER ROWID와 NESTED LOOPS가 나오는지 확인한다. 힌트를 박았으므로 힌트 유효기간 대장에 그 근거(쪽 분량 50줄, 인덱스에 정렬 키가 다 있다)를 적는다.

해설

커버링 인덱스로 하는 답: ORD_ITEM_QTY를 인덱스 끝에 넣으면 테이블에 가지 않고 약 52블록이다. 이번 분기에는 인덱스를 키울 수 없어 쓰지 않았지만 개편 때 비교할 후보다. 247만 행 인덱스의 항목이 커지고, 화면 칸이 하나 늘면 다시 테이블에 간다는 대가가 있다.
키 기반 페이징이 근본 처방이다. 메뉴팀이 200쪽으로 가는 까닭이 "몇 달 전"을 보려는 것이라면 '날짜로 가기'와 「다음」 버튼으로 바꿔 몇 쪽째든 약 5블록이 된다. 개편 때 화면 요구 재협상서로 다룬다(교재 09화 §13).
이 방법도 버릴 줄을 세는 일은 남는다. 리프 한 블록에 약 210개가 들어 있으므로 500쪽(2만 5천 줄)이면 인덱스만 약 120블록이다. 쪽 번호를 지키는 한 이 기울기는 없어지지 않는다(교재 09화 §8).
ROWID는 행의 물리 주소다. 같은 SQL 안에서 고르고 바로 쓰므로 안전하지만, ROWID를 화면에 들고 있다가 다음 요청에 쓰면 테이블을 옮기거나 줄이는 작업 뒤에 엉뚱한 행을 가리킬 수 있다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황