PART-001

석 달만 보는데 24개월 칸을 전부 연다

기초SELECT버거376 / 700

상황

본사 영업기획팀의 「분기 매장 실적」은 연도와 분기를 고르면 매장별 주문 건수를 낸다. 분기 회의 전 주에 수십 번 돌린다. "파티션을 했다는데 왜 이렇게 오래 걸리나." 화면은 :v_yyyy = '2012'와 :v_q = '3'을 넘긴다.

테이블과 인덱스

ORD_RANGE 주문(파티션 복사본): 1,000,000행 · PARTITION BY RANGE (ORD_YM) 월별 25칸. P201201 ~ P201312 24칸에 P_DEFAULT (MAXVALUE) 1칸이 더 있다(비어 있고 세그먼트가 없다).
ORD_YM은 VARCHAR2(6) '201207' 형식의 파티션 키이고, 적재할 때 SUBSTR(ORD_DT,1,6)으로 채웠다. 칸 하나를 FULL로 읽으면 약 1,980블록이다(데이터 약 970블록, 적재 때 생긴 빈 블록 약 1,006블록, 교재 14화 §8.5).
2012년 3분기(7 ~ 9월)는 약 126,000건, 200개 매장이다.
LOCAL 인덱스 ORD_RANGE_PK (ORD_NO, ORD_YM)와 ORD_RANGE_X01 (ORD_DT, ORD_HMS)가 있지만 이 조회에는 쓰이지 않는다(통계 100%).

원본 SQL

sql

SELECT SHOP_NO, COUNT(*) AS ord_cnt
  FROM ORD_RANGE
 WHERE SUBSTR(ORD_YM, 1, 4) = :v_yyyy
   AND TO_CHAR(TO_DATE(ORD_YM, 'YYYYMM'), 'Q') = :v_q
 GROUP BY SHOP_NO
 ORDER BY SHOP_NO

실행계획 학습용 예시 · 교재 14화 규모

xplan

---------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name      | Starts | E-Rows | A-Rows |    A-Time   | Buffers | Pstart | Pstop |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |      1 |        |    200 | 00:00:00.46 |   47460 |        |       |
|   1 |  SORT GROUP BY        |           |      1 |    100 |    200 | 00:00:00.46 |   47460 |        |       |
|   2 |   PARTITION RANGE ALL |           |      1 |    100 |   126K | 00:00:00.43 |   47460 |      1 |    25 |
|*  3 |    TABLE ACCESS FULL  | ORD_RANGE |     24 |    100 |   126K | 00:00:00.42 |   47460 |      1 |    25 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter((SUBSTR("ORD_YM",1,4)=:V_YYYY AND
              TO_CHAR(TO_DATE("ORD_YM",'YYYYMM'),'Q')=:V_Q))

문제

실행계획의 어느 항목에서 파티션을 전부 열었다는 것을 알 수 있는지 쓰고, 왜 전부 열었는지 설명하시오.
결과가 바뀌지 않게 SQL을 고쳐 2012년 3분기의 칸만 열게 하고, 몇 블록이 될지 어림하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 분기 → 월 범위는 화면(바인드) 쪽에서 만든다 : 2012년 3분기 = '201207' ~ '201209'
SELECT SHOP_NO, COUNT(*) AS ord_cnt
  FROM ORD_RANGE
 WHERE ORD_YM BETWEEN :v_from_ym AND :v_to_ym
 GROUP BY SHOP_NO
 ORDER BY SHOP_NO;

-- 화면을 못 고치면 SQL 안에서 '값 쪽' 만 가공한다 (컬럼은 그대로)
--  WHERE ORD_YM BETWEEN :v_yyyy || LPAD(:v_q * 3 - 2, 2, '0')
--                   AND :v_yyyy || LPAD(:v_q * 3, 2, '0')

개선 후 실행계획 학습용 예시 · 교재 14화 규모

xplan

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Starts | E-Rows | A-Rows |    A-Time   | Buffers | Pstart | Pstop |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |      1 |        |    200 | 00:00:00.07 |    5941 |        |       |
|   1 |  SORT GROUP BY              |           |      1 |    200 |    200 | 00:00:00.07 |    5941 |        |       |
|*  2 |   FILTER                    |           |      1 |        |   126K | 00:00:00.05 |    5941 |        |       |
|   3 |    PARTITION RANGE ITERATOR |           |      1 |   126K |   126K | 00:00:00.05 |    5941 |    KEY |   KEY |
|*  4 |     TABLE ACCESS FULL       | ORD_RANGE |      3 |   126K |   126K | 00:00:00.04 |    5941 |    KEY |   KEY |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(:V_FROM_YM<=:V_TO_YM)
   4 - filter(("ORD_YM">=:V_FROM_YM AND "ORD_YM"<=:V_TO_YM))

  (실행 때 고른 칸 — 7 ~ 9번 · P201207 · P201208 · P201209)

모범 답안 서술

2번 줄의 PARTITION RANGE ALL과 Pstart 1 · Pstop 25, 3번 줄의 Starts 24를 보면 칸을 전부 열었다는 것을 알 수 있다(빈 P_DEFAULT는 세그먼트가 없어 시작하지 않았다). 석 달만 읽으면 되는데 24개월 칸을 다 읽어 47,460블록이 들었다. 까닭은 파티션 키 ORD_YM을 SUBSTR과 TO_CHAR(TO_DATE(…))로 가공한 데 있다. 칸막이에는 ORD_YM 값의 구간('201207' 이상 '201208' 미만 …)이 붙어 있는데, 가공한 값('2012', '3')은 칸막이 어디에도 없다. 그래서 옵티마이저는 칸을 고르지 못하고(Pruning 실패) 전부 연 뒤 행마다 함수를 풀어 거른다. 인덱스 컬럼을 가공하면 범위를 잡지 못하는 것과 같은 원리다. 컬럼은 그대로 두고, 분기를 월 범위로 바꾸는 일은 화면(바인드) 쪽에서 한다(ORD_YM BETWEEN '201207' AND '201209'). 바인드 변수라서 Pstart와 Pstop은 KEY로 찍히고, 실행할 때 7 ~ 9번 세 칸만 연다(PARTITION RANGE ITERATOR, Starts 3). Buffers는 47,460 → 약 5,940으로 준다(칸 하나 약 1,980).

해설

ORD_YM은 문자열이다. 'YYYYMM' 고정 6자리라서 문자 범위 비교가 날짜 순서와 같다. 자릿수가 다른 값이 섞이면 이 전제가 깨지지만, 적재할 때 SUBSTR(ORD_DT,1,6)으로 채우는 한 전제는 지켜진다.
칸을 고른 뒤에는 칸 안을 얼마나 읽는지가 다시 문제다. 석 달 5,940블록 중 약 3,000블록은 적재 때 생긴 빈 블록이다(교재 14화 §8.5, 첫 익스텐트 8MB, 처방은 INITIAL 명시).
화면이 일자(ORD_DT)로 묻는 경우에는 ORD_DT가 파티션 키가 아니라서 칸을 고르지 못한다(교재 14화 §7.3, ORD_DT LIKE '201206%' 41,897). 이때는 ORD_YM 조건을 같이 주거나 키를 다시 설계한다.
리터럴이면 Pstart와 Pstop이 7 / 9로, 바인드 변수면 KEY / KEY로 찍힌다. 둘 다 Pruning이다. KEY가 보이면 실행할 때 고른다는 뜻이지 실패가 아니다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 교재 14화 규모 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 교재 14화 규모 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황