PART-029

칸을 하나만 여는데도 한 달 조회가 한 해 치를 읽는다

기초DDL버거404 / 700

상황

본사 분석팀이 주문을 주문일 기준 월별 파티션 ORD_RANGE_DT로 새로 지었다. 한빛유통 T_ORD_BIG_P14의 생성 스크립트를 본떠 쓰면서, DATE 리터럴 자리에 같은 날짜를 문자열로 넣었다. 그런데 「월 매장 실적」이 기존 ORD_RANGE보다 오히려 느리다. 조민우: "실행계획을 보면 칸 하나만 열거든요. 파티션은 잘 타는데요."

테이블과 인덱스

ORD_RANGE_DT 주문(파티션 복사본, 문제 설정): ORD 1,000,000행을 PARTITION BY RANGE (ORD_DT) 월별 24칸(P201201 ~ P201312)과 PMAX로 나눠 CTAS로 지었다. 경계는 아래 DDL과 같다.
ORD_DT는 VARCHAR2(8) '20120601' 형식이다. 2012년은 약 500,700행, 2012년 6월은 41,040행이다. 같은 데이터를 한 달씩 제대로 나누면 칸 하나는 FULL로 약 935블록이다.
인덱스는 없다. 통계는 최신이다.

원본 SQL

sql

CREATE TABLE ORD_RANGE_DT
PARTITION BY RANGE (ORD_DT)
( PARTITION P201201 VALUES LESS THAN ('2012-02-01'),
  PARTITION P201202 VALUES LESS THAN ('2012-03-01'),
  -- … 월마다 한 줄
  PARTITION P201212 VALUES LESS THAN ('2013-01-01'),
  PARTITION P201301 VALUES LESS THAN ('2013-02-01'),
  -- …
  PARTITION P201312 VALUES LESS THAN ('2014-01-01'),
  PARTITION PMAX    VALUES LESS THAN (MAXVALUE) )
AS SELECT * FROM ORD;

-- 「월 매장 실적」 (:v_from = '20120601', :v_to = '20120630')
SELECT SHOP_NO, COUNT(*) AS ord_cnt
  FROM ORD_RANGE_DT
 WHERE ORD_DT BETWEEN :v_from AND :v_to
 GROUP BY SHOP_NO;

실행계획 학습용 예시

xplan

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |      1 |        |    200 | 00:00:00.21 |   11236 |        |       |
|   1 |  HASH GROUP BY              |              |      1 |    200 |    200 | 00:00:00.21 |   11236 |        |       |
|*  2 |   FILTER                    |              |      1 |        |  41040 | 00:00:00.19 |   11236 |        |       |
|   3 |    PARTITION RANGE ITERATOR |              |      1 |  41012 |  41040 | 00:00:00.19 |   11236 |    KEY |   KEY |
|*  4 |     TABLE ACCESS FULL       | ORD_RANGE_DT |      1 |  41012 |  41040 | 00:00:00.18 |   11236 |    KEY |   KEY |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(:V_FROM<=:V_TO)
   4 - filter(("ORD_DT">=:V_FROM AND "ORD_DT"<=:V_TO))

  (실행 때 고른 칸 — 12번 P201212)

[USER_TAB_PARTITIONS · 통계 수집 뒤]
  PARTITION_NAME       POSITION  HIGH_VALUE        NUM_ROWS   BLOCKS
  P201201 ~ P201211      1 ~ 11  '2012-02-01' …           0        0
  P201212                    12  '2013-01-01'       500,684   11,230
  P201301 ~ P201311     13 ~ 23  '2013-02-01' …           0        0
  P201312                    24  '2014-01-01'       499,316   11,192
  PMAX                       25  MAXVALUE                 0        0

문제

칸 하나만 열었는데 한 달 치의 열 배가 넘는 블록을 읽은 까닭을 파티션 통계와 경계값으로 설명하시오.
한 칸에 한 달 치만 들어가도록 DDL을 고쳐 쓰고, 고친 뒤 6월 조회가 몇 블록을 읽을지와 지은 뒤 무엇을 확인할지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 경계를 키와 같은 형식('YYYYMMDD')으로 다시 쓴다 · CTAS 로 다시 짓는다 (100만 행을 옮기는 SPLIT 보다 단순하다)
CREATE TABLE ORD_RANGE_DT2
PARTITION BY RANGE (ORD_DT)
( PARTITION P201201 VALUES LESS THAN ('20120201'),
  PARTITION P201202 VALUES LESS THAN ('20120301'),
  PARTITION P201203 VALUES LESS THAN ('20120401'),
  PARTITION P201204 VALUES LESS THAN ('20120501'),
  PARTITION P201205 VALUES LESS THAN ('20120601'),
  PARTITION P201206 VALUES LESS THAN ('20120701'),
  PARTITION P201207 VALUES LESS THAN ('20120801'),
  PARTITION P201208 VALUES LESS THAN ('20120901'),
  PARTITION P201209 VALUES LESS THAN ('20121001'),
  PARTITION P201210 VALUES LESS THAN ('20121101'),
  PARTITION P201211 VALUES LESS THAN ('20121201'),
  PARTITION P201212 VALUES LESS THAN ('20130101'),
  PARTITION P201301 VALUES LESS THAN ('20130201'),
  PARTITION P201302 VALUES LESS THAN ('20130301'),
  PARTITION P201303 VALUES LESS THAN ('20130401'),
  PARTITION P201304 VALUES LESS THAN ('20130501'),
  PARTITION P201305 VALUES LESS THAN ('20130601'),
  PARTITION P201306 VALUES LESS THAN ('20130701'),
  PARTITION P201307 VALUES LESS THAN ('20130801'),
  PARTITION P201308 VALUES LESS THAN ('20130901'),
  PARTITION P201309 VALUES LESS THAN ('20131001'),
  PARTITION P201310 VALUES LESS THAN ('20131101'),
  PARTITION P201311 VALUES LESS THAN ('20131201'),
  PARTITION P201312 VALUES LESS THAN ('20140101'),
  PARTITION PMAX    VALUES LESS THAN (MAXVALUE) )
AS SELECT * FROM ORD;

-- 지은 뒤 첫 점검 : 칸마다 한 달 치(약 41,000행)가 들어갔는가
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORD_RANGE_DT2')
SELECT partition_name, high_value, num_rows, blocks
  FROM user_tab_partitions
 WHERE table_name = 'ORD_RANGE_DT2'
 ORDER BY partition_position;

-- 확인한 뒤 바꿔 끼운다
DROP TABLE ORD_RANGE_DT PURGE;
ALTER TABLE ORD_RANGE_DT2 RENAME TO ORD_RANGE_DT;

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

xplan

[「월 매장 실적」 · 2012년 6월]
------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |      1 |        |    200 | 00:00:00.03 |     936 |        |       |
|   1 |  HASH GROUP BY              |              |      1 |    200 |    200 | 00:00:00.03 |     936 |        |       |
|*  2 |   FILTER                    |              |      1 |        |  41040 | 00:00:00.02 |     936 |        |       |
|   3 |    PARTITION RANGE ITERATOR |              |      1 |  41040 |  41040 | 00:00:00.02 |     936 |    KEY |   KEY |
|*  4 |     TABLE ACCESS FULL       | ORD_RANGE_DT |      1 |  41040 |  41040 | 00:00:00.02 |     936 |    KEY |   KEY |
------------------------------------------------------------------------------------------------------------------------

  (실행 때 고른 칸 — 6번 P201206)

[USER_TAB_PARTITIONS · 다시 지은 뒤]
  P201201 ~ P201312   칸마다 약 39,000 ~ 42,500행 · 약 890 ~ 970블록 · PMAX 0행

모범 답안 서술

4번 줄은 Starts 1이고 Pstart, Pstop은 KEY다. 칸 하나만 열었으니 계획만 보면 파티션을 잘 탄 것처럼 보인다. 그런데 그 한 칸에서 11,236블록을 읽었다. 한 달 치를 제대로 나눈 칸은 약 935블록이니 열 배가 넘는다. 파티션 통계(NUM_ROWS)를 보면 까닭이 드러난다. 행은 12번 칸 P201212(500,684행)와 24번 칸 P201312(499,316행)에만 있고 나머지 22칸은 비어 있다. 6월을 찾아간 칸은 2012년 한 해 치를 안고 있었다. 경계를 '2012-02-01'처럼 하이픈이 든 문자열로 쓴 것이 원인이다. ORD_DT는 VARCHAR2라서 키와 경계를 문자열로 비교하고, 문자열은 앞 글자부터 한 글자씩 비교한다. '20120115'와 '2012-02-01'은 네 글자까지 같고 다섯 번째 글자에서 '0'(48)과 '-'(45)로 갈리는데, '-'가 '0'보다 작다. 그래서 2012년의 모든 날짜는 '2012-…' 경계 열한 개보다 크고 '2013-01-01'보다는 작다(네 번째 글자 '2' < '3'). 2012년 행은 모두 P201212로, 2013년 행은 모두 P201312로 갔다. 옵티마이저도 같은 비교로 칸을 고르므로 '20120601' ~ '20120630'은 12번 칸을 가리킨다. 경계를 키와 같은 형식('YYYYMMDD' 8자리)으로 다시 쓴다. VALUES LESS THAN ('20120201')처럼 같은 자릿수의 숫자 문자열끼리 비교하면 문자 순서가 날짜 순서와 같다. 테이블은 CTAS로 다시 짓는다(50만 행씩 옮겨 쓰는 SPLIT보다 단순하다). 지은 뒤에는 통계를 모아 칸마다 NUM_ROWS가 한 달 치(약 41,000행)인지 확인하고 이름을 바꿔 끼운다. 6월 조회는 6번 칸 하나를 읽어 11,236 → 약 936블록으로 준다.

해설

문자 범위 비교가 날짜 순서와 같은 것은 같은 자릿수의 숫자만 든 문자열끼리 비교할 때뿐이다(교재 03화 §2). 경계에 -가 끼면 그 전제가 깨진다.
에러가 하나도 없다. VARCHAR2 키에 문자 경계라서 DDL도 CTAS도 통과하고, 계획은 칸 하나를 연다. 교재 14화가 경고한 "계획은 SINGLE로 멀쩡한데 그 한 칸이 뚱뚱한" 종류다. 그래서 파티션을 지은 뒤 첫 점검은 칸마다 행 수다.
키가 DATE라면 경계도 TO_DATE('2012-02-01', 'YYYY-MM-DD') 같은 DATE로 쓴다. 키 타입과 형식에 경계를 맞춘다는 원칙은 같다.
다른 길: 문자 날짜 대신 DATE 키를 새로 두면 INTERVAL까지 쓸 수 있지만, 화면의 ORD_DT 조건을 모두 새 키로 바꿔야 칸을 고른다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황