공통 뷰를 거치는 매장 월 주문 목록이 주문 테이블을 통째로 읽는다

기초SELECT버거280 / 700

상황

버거 본사의 「매장 월 주문 목록」은 매장과 연월을 고르면 그 매장의 한 달 주문을 시간순으로 보여 준다. 슈퍼바이저 30명이 하루 1,500번쯤 열고, 한 번에 0.2초 남짓 걸린다. SQL은 2014년에 월 마감 리포트용으로 만든 공통 뷰 V_ORD_MON을 읽는다. 개발자는 "뷰를 거쳐서 느린 것 같으니 뷰를 빼고 ORD를 직접 읽게 고치겠다"라고 한다.

테이블과 인덱스

ORD 주문: 1,000,000행 · 22,422블록 · ORD_DT VARCHAR2(8) '20120101' ~ '20131231' · 매장 200곳 · 한 달 약 41,700건 · 한 매장의 한 달 주문 약 208건
인덱스 ORD_PK (ORD_NO) · ORD_X01 (ORD_DT, ORD_HMS): 날짜순 적재(CF 22,223) · 높이 3 · 한 달치 리프 약 140블록
SHOP_NO에는 인덱스가 없다 · 통계 최신
뷰 V_ORD_MON의 정의는 아래 SQL 맨 위와 같다 · 실행계획은 :shop = 'SH0042', :ym = '201306'

원본 SQL

sql

-- 공통 뷰 (2014년 월 마감 리포트용)
CREATE OR REPLACE VIEW V_ORD_MON AS
SELECT ORD_NO, SHOP_NO,
       SUBSTR(ORD_DT, 1, 6) AS ORD_YM,
       ORD_DT, ORD_HMS
  FROM ORD;

-- 매장 월 주문 목록
SELECT ORD_NO, ORD_DT, ORD_HMS
  FROM V_ORD_MON
 WHERE SHOP_NO = :shop
   AND ORD_YM  = :ym
 ORDER BY ORD_DT, ORD_HMS;

실행계획 학습용 예시

xplan

---------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |    208 |00:00:00.21 |   22422 |          |
|   1 |  SORT ORDER BY     |      |      1 |     50 |    208 |00:00:00.21 |   22422 |18432  (0)|
|*  2 |   TABLE ACCESS FULL| ORD  |      1 |     50 |    208 |00:00:00.21 |   22422 |          |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("SHOP_NO"=:SHOP AND SUBSTR("ORD_DT",1,6)=:YM))

문제

실행계획에 뷰를 읽는 줄이 보이지 않는 이유와, 2번 줄 조건이 SUBSTR("ORD_DT",1,6)=:YM으로 바뀐 이유를 설명하시오.
개발자의 제안(뷰를 빼고 ORD를 직접 읽는다)으로 빨라지는지 판단하고, 화면 SQL을 고쳐 ORD_X01로 한 달만 읽도록 하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 뷰는 그대로 둔다. 가공된 ORD_YM 대신 원래 컬럼 ORD_DT에 범위를 준다
SELECT ORD_NO, ORD_DT, ORD_HMS
  FROM V_ORD_MON
 WHERE SHOP_NO = :shop
   AND ORD_DT BETWEEN :ym || '01' AND :ym || '31'
 ORDER BY ORD_DT, ORD_HMS;

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

xplan

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |         |      1 |        |    208 |00:00:00.02 |    1077 |          |
|   1 |  SORT ORDER BY                        |         |      1 |    208 |    208 |00:00:00.02 |    1077 |18432  (0)|
|*  2 |   FILTER                              |         |      1 |        |    208 |00:00:00.02 |    1077 |          |
|*  3 |    TABLE ACCESS BY INDEX ROWID BATCHED| ORD     |      1 |    208 |    208 |00:00:00.02 |    1077 |          |
|*  4 |     INDEX RANGE SCAN                  | ORD_X01 |      1 |  41667 |  41712 |00:00:00.01 |     143 |          |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(:YM||'01'<=:YM||'31')
   3 - filter("SHOP_NO"=:SHOP)
   4 - access("ORD_DT">=:YM||'01' AND "ORD_DT"<=:YM||'31')

모범 답안 서술

실행계획에 VIEW 줄이 없다. 조인이나 필터만 있는 단순한 뷰라서 옵티마이저가 뷰를 바깥 SQL에 합쳤기 때문이다(단순 뷰 머징). 뷰가 합쳐지면 바깥 조건의 뷰 컬럼은 뷰 정의의 식으로 바뀐다. 그래서 ORD_YM = :ym이 SUBSTR("ORD_DT",1,6) = :YM이 되었고, 컬럼이 가공된 조건이라 ORD_X01에서 범위를 찾지 못하고 2번 줄처럼 테이블 전체를 읽으며 걸렀다(Buffers 22,422). 뷰 자체는 원인이 아니다. 뷰를 빼고 ORD를 직접 읽어도 SUBSTR(ORD_DT, 1, 6) = :ym으로 쓰면 계획은 똑같다. 고칠 곳은 조건을 거는 컬럼이다. 뷰는 그대로 두고 원래 컬럼에 ORD_DT BETWEEN :ym || '01' AND :ym || '31'로 범위를 준다. 문자 날짜라도 8자리 문자열은 날짜순으로 정렬되므로 이 범위가 정확히 한 달이다. 이제 ORD_X01에서 한 달치 41,712건만 읽고(143블록), 날짜순으로 쌓인 테이블 블록 약 934개에서 매장 조건을 거른다. Buffers는 22,422 → 1,077로 준다. 더 줄이려면 (SHOP_NO, ORD_DT) 인덱스를 만들어 그 매장의 208건만 읽게 할 수 있다(약 210블록). 다만 100만 행 인덱스가 하나 더 생겨 주문을 넣을 때마다 고쳐야 하므로, 하루 1,500번 호출과 적재 비용을 함께 따져 정한다.

해설

뷰 머징의 뜻: 머징은 뷰 이름을 정의로 바꿔 끼우는 일이다. 컬럼 이름이 그럴듯해도 정의가 식이면 바깥 조건도 그 식에 걸린다. 공통 뷰에 가공 컬럼을 둘 때는 원래 컬럼도 함께 내보내고, 조건은 원래 컬럼에 걸도록 안내한다.
다른 정답: ORD_DT LIKE :ym || '%'도 인덱스 범위 스캔이 된다. ORD_DT >= :ym || '01' AND ORD_DT < 다음 달 || '01'처럼 상한을 다음 달 1일 미만으로 주는 방식도 맞다.
함수 기반 인덱스 (SUBSTR(ORD_DT, 1, 6))도 답은 되지만, 가공 방식을 인덱스로 굳히고 인덱스를 하나 더 늘린다. SQL을 고칠 수 있으면 SQL을 고친다(교재 02화, 03화).
뷰가 머징되지 않는 경우(GROUP BY 뷰 등)에도 바깥 조건은 뷰 안으로 밀려 들어가며(조건절 Pushing), 이때도 뷰 컬럼은 정의의 식으로 바뀐다. 결과는 같다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황