많이 팔리는 메뉴는 「최근 판매 50건」이 바로 뜨는데 시즌 메뉴를 고르면 화면이 멈춘다

중급SELECT유통318 / 700

상황

본사 메뉴팀의 「메뉴별 최근 판매」 화면은 메뉴를 고르면 그 메뉴가 담긴 최근 판매 50줄(판매번호, 수량, 단가)을 보여 준다. (ICE)아메리카노를 고르면 바로 뜨는데, 2022년 겨울에만 팔고 내린 시즌 메뉴를 고르면 2초 가까이 걸린다. 개발자는 "둘 다 인덱스를 탔고 실행계획 모양도 똑같다"라고 한다. 메뉴팀은 신메뉴를 등록한 첫날에도 이 화면을 자주 연다.

테이블과 인덱스

CF11 판매상품: 1,096,708행 · 8,204블록 · SLE_NO VARCHAR2(15) '20231231-0612'(판매일 + 그날 일련번호, 판매 순서대로 매겨진다) · 판매번호 순서대로 적재되어 있다
CF11_PK (SLE_NO, ITM_NO): BLEVEL 2 · 리프 약 4,290블록 · 다른 인덱스 없음 · 통계 최신(ITM_NO 히스토그램 없음, 팔린 메뉴 88종)
(ICE)아메리카노는 275,135줄(25%)이다. 시즌 메뉴 하나는 2022년 12월 ~ 2023년 2월에만 팔려 3,032줄이고 마지막 판매는 2023-02-28이다. 판매 데이터는 2023-12-31까지다
인덱스의 오래된 구간은 버퍼 캐시에 거의 없어서 디스크에서 한 블록씩 읽는다

원본 SQL

sql

SELECT SLE_NO, SLE_QTY, SLE_UPRC
  FROM CF11
 WHERE ITM_NO = :itm
 ORDER BY SLE_NO DESC
 FETCH FIRST 50 ROWS ONLY

실행계획 학습용 예시

xplan

[(ICE)아메리카노]
----------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |      1 |        |     50 |00:00:00.01 |       5 |     0 |
|*  1 |  VIEW                         |         |      1 |     50 |     50 |00:00:00.01 |       5 |     0 |
|*  2 |   WINDOW NOSORT STOPKEY       |         |      1 |     50 |     50 |00:00:00.01 |       5 |     0 |
|   3 |    TABLE ACCESS BY INDEX ROWID| CF11    |      1 |  12463 |     50 |00:00:00.01 |       5 |     0 |
|*  4 |     INDEX FULL SCAN DESCENDING| CF11_PK |      1 |     50 |     50 |00:00:00.01 |       3 |     0 |
----------------------------------------------------------------------------------------------------------

[시즌 메뉴 · 마지막 판매 2023-02-28]
----------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |      1 |        |     50 |00:00:01.62 |    1827 |  1795 |
|*  1 |  VIEW                         |         |      1 |     50 |     50 |00:00:01.62 |    1827 |  1795 |
|*  2 |   WINDOW NOSORT STOPKEY       |         |      1 |     50 |     50 |00:00:01.62 |    1827 |  1795 |
|   3 |    TABLE ACCESS BY INDEX ROWID| CF11    |      1 |  12463 |     50 |00:00:01.62 |    1827 |  1795 |
|*  4 |     INDEX FULL SCAN DESCENDING| CF11_PK |      1 |     50 |     50 |00:00:01.60 |    1810 |  1790 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=50)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("SLE_NO") DESC )<=50)
   4 - filter("ITM_NO"=:ITM)

문제

두 실행계획은 모양도 같고 A-Rows도 같은데 Buffers가 5와 1,827로 갈린 까닭을 4번 줄의 조건(access인지 filter인지)으로 설명하시오. 판매가 50줄도 안 되는 신메뉴를 고르면 어떻게 되는지도 쓰시오.
어떤 메뉴를 골라도 50줄만 읽고 멈추도록 인덱스를 제안하고, 그 인덱스의 대가를 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 메뉴 하나의 판매가 판매번호 순서로 모인 인덱스 · 화면 칸(수량, 단가)까지 담아 테이블에 안 간다
CREATE INDEX CF11_X01 ON CF11 (ITM_NO, SLE_NO, SLE_QTY, SLE_UPRC);

-- SQL 은 그대로 : ITM_NO = 가 access 로 바뀌고, 그 범위를 거꾸로 읽다 50 줄에서 멈춘다
SELECT SLE_NO, SLE_QTY, SLE_UPRC
  FROM CF11
 WHERE ITM_NO = :itm
 ORDER BY SLE_NO DESC
 FETCH FIRST 50 ROWS ONLY

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

xplan

[어느 메뉴든]
--------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |      1 |        |     50 |00:00:00.01 |       4 |
|*  1 |  VIEW                         |          |      1 |     50 |     50 |00:00:00.01 |       4 |
|*  2 |   WINDOW NOSORT STOPKEY       |          |      1 |     50 |     50 |00:00:00.01 |       4 |
|*  3 |    INDEX RANGE SCAN DESCENDING| CF11_X01 |      1 |  12463 |     50 |00:00:00.01 |       4 |
--------------------------------------------------------------------------------------------------------

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

모범 답안 서술

4번 줄은 CF11_PK를 판매번호 역순으로 처음부터 훑는 INDEX FULL SCAN DESCENDING이고, ITM_NO = :itm은 범위를 정하는 access가 아니라 지나가는 항목마다 확인하는 filter다. 2번 줄 STOPKEY는 조건을 통과한 행이 50줄이 되어야 멈춘다. 그래서 몇 항목을 지나야 하는지는 그 메뉴의 비율과 마지막 판매 시점이 정한다. (ICE)아메리카노는 전체의 25%라서 최근 약 200항목 안에 50줄이 있고 리프 한 블록으로 끝난다(Buffers 5). 시즌 메뉴는 마지막 판매가 2023-02-28이라 그 뒤 열 달 치 약 46만 항목(리프 약 1,800블록)을 다 거른 뒤에야 첫 줄을 만난다(Buffers 1,827). 오래된 구간의 리프를 디스크에서 한 블록씩 읽어 1.6초가 걸렸다. A-Rows는 걸러진 뒤의 숫자라 둘 다 50이고, 차이는 Buffers와 Reads에만 드러난다. 판매가 50줄이 안 되는 신메뉴를 고르면 STOPKEY에 끝내 닿지 못하므로 인덱스 전체(리프 약 4,290블록)를 처음부터 끝까지 읽는다. 같은 SQL, 같은 실행계획이 바인드 값에 따라 5블록도 되고 4,300블록도 된다. (ITM_NO, SLE_NO, ...) 인덱스를 만들면 ITM_NO = :itm이 access가 되어 그 메뉴의 범위로 곧장 간다. 범위 안은 판매번호 순서이므로 거꾸로(RANGE SCAN DESCENDING) 읽다가 50줄에서 멈춘다. 화면 칸인 SLE_QTY, SLE_UPRC까지 담으면 테이블에도 가지 않는다. 어느 메뉴든 약 4블록이고, 시즌 메뉴는 1,827 → 4로 준다. 대가는 판매 줄이 들어올 때마다 고칠 인덱스가 하나 더 느는 적재 비용(하루 약 1,500줄)과 인덱스 공간(약 5,000블록)이다. 메뉴별 집계처럼 ITM_NO로 찾는 다른 SQL도 이 인덱스를 함께 쓸 수 있어서 만들 가치가 있다.

해설

같은 계획, 다른 일량: 실행계획의 모양과 A-Rows만 보면 두 경우를 구별할 수 없다. 부분범위 처리에 filter 조건이 끼면 '몇 행을 지나서야 N행이 모이는가'가 일량을 정한다. 대표 값을 바꿔 가며 Buffers를 재 본다(교재 09화 §3).
옵티마이저는 메뉴 88종이 고르게 팔린다고 보고(E-Rows 12,463) 50줄을 약 4,400항목 안에서 찾을 것이라 계산했다. 히스토그램이 있어도 '마지막 판매가 언제였나'는 통계에 없으므로 이 오판은 남는다.
(ITM_NO, SLE_NO) 두 컬럼만으로도 범위 스캔이 되지만 테이블을 50번 찾아간다. 시즌 메뉴의 마지막 50줄은 하루 반 사이에 모여 있어 약 17블록이고, 아메리카노는 2 ~ 3블록이다. 커버링으로 갈지는 인덱스 크기와 견줘 정한다.
IDX-006의 커버링 인덱스 (ITM_NO, SLE_QTY, SLE_UPRC)는 이 화면에 쓸 수 없다. 둘째 컬럼이 SLE_NO가 아니라서 판매번호 순서를 모르기 때문이다. 두 화면을 함께 보고 (ITM_NO, SLE_NO, SLE_QTY, SLE_UPRC) 하나로 합치는 편이 낫다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황