JOIN-047

키오스크가 메뉴판 한 장을 띄울 때마다 가격 이력 5천 블록을 읽는다

기초SELECT버거197 / 700

상황

매장 키오스크는 화면이 켜질 때와 주문이 끝날 때마다 판매 중인 메뉴의 오늘 가격을 다시 읽는다. 매장 200곳의 키오스크가 하루 3만 번쯤 호출한다. 본사는 주간 행사가를 운영하려고 2012년부터 매주 월요일에 전 메뉴의 그 주 가격을 한 줄씩 올린다(값이 같아도 올린다). 개발자는 "가격표가 겨우 92줄인데 왜 무거운지 모르겠다"라고 한다. 아래는 2013-12-31(화요일)에 뜬 실행계획이다.

테이블과 인덱스

ITEM 상품: 92행 · 5블록 · ITEM_PK (ITEM_ID) · 판매 중(SALE_YN = 'Y') 84개
ITEM_PRC_HIS 메뉴 가격 이력 (이 문제에서 쓰는 파생 테이블, 선분이력): ITEM_ID · STA_DT · END_DT (둘 다 VARCHAR2(8)) · SALE_PRC · 9,568행(92개 × 104주) · 60블록
지난 구간의 END_DT는 그 주 일요일이고, 지금 구간의 END_DT는 '99991231'이다 · 구간은 겹치거나 끊기지 않는다 · 아직 다음 주 가격을 미리 올리는 기능은 없다
인덱스는 ITEM_PRC_HIS_PK (ITEM_ID, STA_DT)뿐이다 · BLEVEL 1 · 리프 약 30블록 · 주마다 92행씩 쌓여서 메뉴 하나의 104행은 약 60블록에 흩어져 있다 · 통계 최신

원본 SQL

sql

SELECT t.ITEM_ID, t.ITEM_NM, h.SALE_PRC
  FROM ITEM t, ITEM_PRC_HIS h
 WHERE t.SALE_YN = 'Y'
   AND h.ITEM_ID = t.ITEM_ID
   AND :v_today BETWEEN h.STA_DT AND h.END_DT
 ORDER BY t.ITEM_ID

실행계획 학습용 예시

xplan

--------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                 |      1 |        |     84 |00:00:00.02 |    5213 |
|   1 |  SORT ORDER BY                |                 |      1 |     84 |     84 |00:00:00.02 |    5213 |
|   2 |   NESTED LOOPS                |                 |      1 |     84 |     84 |00:00:00.02 |    5213 |
|   3 |    NESTED LOOPS               |                 |      1 |   4368 |   8736 |00:00:00.01 |     173 |
|*  4 |     TABLE ACCESS FULL         | ITEM            |      1 |     84 |     84 |00:00:00.01 |       5 |
|*  5 |     INDEX RANGE SCAN          | ITEM_PRC_HIS_PK |     84 |     52 |   8736 |00:00:00.01 |     168 |
|*  6 |    TABLE ACCESS BY INDEX ROWID| ITEM_PRC_HIS    |   8736 |      1 |     84 |00:00:00.01 |    5040 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("T"."SALE_YN"='Y')
   5 - access("H"."ITEM_ID"="T"."ITEM_ID" AND "H"."STA_DT"<=:V_TODAY)
   6 - filter("H"."END_DT">=:V_TODAY)

문제

메뉴 84개의 가격을 얻는 데 5,213블록을 읽은 까닭을 5번과 6번 줄의 A-Rows와 Predicate Information으로 설명하시오.
오늘 가격을 메뉴마다 한 줄만 읽어 찾도록 SQL과 인덱스를 고치시오. 나중에 다음 주 가격을 미리 올리는 기능이 생기면 고친 SQL이 어떻게 되는지도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 종료일을 메뉴 바로 뒤에 둔 인덱스: 지금 구간과 그 뒤 구간만 앞쪽에 모인다
CREATE INDEX ITEM_PRC_HIS_X01 ON ITEM_PRC_HIS (ITEM_ID, END_DT);

-- 지금 구간은 종료일이 '99991231' 하나뿐이다: 메뉴마다 등치 한 건
SELECT t.ITEM_ID, t.ITEM_NM, h.SALE_PRC
  FROM ITEM t, ITEM_PRC_HIS h
 WHERE t.SALE_YN = 'Y'
   AND h.ITEM_ID = t.ITEM_ID
   AND h.END_DT  = '99991231'
 ORDER BY t.ITEM_ID;

-- 다음 주 가격을 미리 올리는 기능이 생기면 등치를 범위로 바꾼다 (같은 인덱스로 지금 구간과 예약 구간 한두 건만 읽는다)
--   AND h.END_DT >= :v_today
--   AND h.STA_DT <= :v_today

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

xplan

---------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                  |      1 |        |     84 |00:00:00.01 |     257 |
|   1 |  SORT ORDER BY                |                  |      1 |     84 |     84 |00:00:00.01 |     257 |
|   2 |   NESTED LOOPS                |                  |      1 |     84 |     84 |00:00:00.01 |     257 |
|   3 |    NESTED LOOPS               |                  |      1 |     84 |     84 |00:00:00.01 |     173 |
|*  4 |     TABLE ACCESS FULL         | ITEM             |      1 |     84 |     84 |00:00:00.01 |       5 |
|*  5 |     INDEX RANGE SCAN          | ITEM_PRC_HIS_X01 |     84 |      1 |     84 |00:00:00.01 |     168 |
|   6 |    TABLE ACCESS BY INDEX ROWID| ITEM_PRC_HIS     |     84 |      1 |     84 |00:00:00.01 |      84 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("T"."SALE_YN"='Y')
   5 - access("H"."ITEM_ID"="T"."ITEM_ID" AND "H"."END_DT"='99991231')

모범 답안 서술

5번 줄의 access 조건은 "H"."STA_DT"<=:V_TODAY 하나다. 인덱스 (ITEM_ID, STA_DT)에서 시작일이 오늘 이전인 구간은 2012년부터 쌓인 104구간 전부라서, 메뉴 84개마다 104항목씩 8,736항목을 읽었다. 종료일 조건은 인덱스에 없어서 6번 줄에서 테이블에 8,736번 가서야 filter("H"."END_DT">=:V_TODAY)로 거르고, 남은 것은 메뉴마다 한 줄(84행)이다. 메뉴 하나의 104행이 약 60블록에 흩어져 있어서 테이블에서만 5,040블록을 읽었다. 가격 이력은 매주 92줄씩 늘어나므로 이 일량도 해마다 커진다. 지금 유효한 구간은 메뉴마다 하나이고 그 종료일은 모두 '99991231'이다. 그래서 오늘 가격은 h.END_DT = '99991231'이라는 등치 조건으로 찾을 수 있다. (ITEM_ID, END_DT) 인덱스를 만들면 메뉴마다 인덱스 한 항목과 테이블 한 행만 읽는다. Buffers는 5,213 → 257로 준다(하루 3만 번이면 약 1억 5,600만 → 771만 블록). 다음 주 가격을 미리 올리는 기능이 생기면 등치 조건은 틀린다. 예약한 구간이 '99991231'을 갖고, 이번 주 구간은 종료일이 일요일로 닫히므로 키오스크에 다음 주 가격이 뜬다. 그때는 h.END_DT >= :v_today AND h.STA_DT <= :v_today로 바꾼다. 같은 (ITEM_ID, END_DT) 인덱스에서 종료일이 오늘 이후인 구간은 지금 구간과 예약 구간 한두 개뿐이라 일량은 거의 같다.

해설

함정: 인덱스를 (ITEM_ID, STA_DT, END_DT)로 바꾸면 테이블 액세스는 84번으로 줄지만, 인덱스에서 여전히 메뉴마다 104항목을 읽고 거른다. 범위 조건이 둘일 때 뒤 칸은 스캔 범위를 줄이지 못한다. 최근 시점을 묻는 화면에는 종료일을 앞에 둔다.
다른 정답: 인덱스를 새로 만들지 않고 메뉴마다 ITEM_PRC_HIS_PK를 역순으로 읽다가 첫 행에서 멈추게 할 수도 있다(STA_DT <= :v_today ORDER BY STA_DT DESC FETCH FIRST 1 ROW ONLY를 OUTER APPLY로). 구간이 겹치지 않는다는 전제가 있어야 맞는 답이 된다.
대가: 인덱스 하나가 늘지만 가격 이력은 주에 92행씩만 들어오므로 적재 부담은 거의 없다. 대신 이 인덱스를 쓰는 화면이 '지금 구간의 종료일은 99991231'이라는 약속에 기대므로, 가격 등록 프로그램이 지난 구간을 닫지 않으면 메뉴가 두 줄씩 나온다.
이력의 지금 값을 화면마다 자주 읽는다면 ITEM에 현재가 칸을 두는 반정규화도 있다. 읽기는 가장 싸지만 가격 등록 때 두 곳을 함께 고쳐야 한다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황