파티션 프루닝(Partition Pruning)이란 파티셔닝 컬럼이 조건절에 조건으로 들어왔을 때 해당 파티션만 읽고 대상이 아닌 파티션을 스캔하지 않는 기법을 말한다.
예를 들어
거래 일자 기준으로 파티셔닝된 대용량 테이블이 있다고 가정한다.
조건절에 거래일자가 없거나 묵시적 형 변환이 발생하면 테이블 내에 모든 파티션을 스캔해야 한다.(PARTITION RANGE ALL)
즉, 파티션 프루닝이 되지 않습니다.
조건절에 거래일자가 있으면 해당 파티션만 읽고 스캔을 멈추게 된다.(PARTITION RANGE ITERATOR)
즉, 파티션 프루닝이 동작하게 된다.
파티션 프루닝이 동작하도록 조건절 튜닝하기
테이블 생성
CREATE TABLE TB_TRD
(
TRD_NO VARCHAR2 (15), -- 거래번호
TRD_DT VARCHAR2 (8), -- 거래일자
TRD_AMT NUMBER (15), -- 거래금액
CUST_ID VARCHAR2 (10), -- 고객ID
PRDT_CD VARCHAR2 (6), -- 상품코드
INST_ID VARCHAR2 (50), -- 입력자ID
INST_DTM DATE -- 입력일시
)
PARTITION BY RANGE (TRD_DT)
(
PARTITION P_TB_TRD_201409 VALUES LESS THAN ('20141001'),
PARTITION P_TB_TRD_201410 VALUES LESS THAN ('20141101'),
PARTITION P_TB_TRD_201411 VALUES LESS THAN ('20141201'),
PARTITION P_TB_TRD_201412 VALUES LESS THAN ('20150101'),
PARTITION P_TB_TRD_201501 VALUES LESS THAN ('20150201'),
PARTITION P_TB_TRD_201502 VALUES LESS THAN ('20150301'),
PARTITION P_TB_TRD_201503 VALUES LESS THAN ('20150401'),
PARTITION P_TB_TRD_201504 VALUES LESS THAN ('20150501'),
PARTITION P_TB_TRD_201505 VALUES LESS THAN ('20150601'),
PARTITION P_TB_TRD_201506 VALUES LESS THAN ('20150701'),
PARTITION P_TB_TRD_201507 VALUES LESS THAN ('20150801'),
PARTITION P_TB_TRD_201508 VALUES LESS THAN ('20150901'),
PARTITION P_TB_TRD_201509 VALUES LESS THAN ('20151001'),
PARTITION P_TB_TRD_MAX VALUES LESS THAN (MAXVALUE)
);
CREATE TABLE DUAL_10
(
DUMMY VARCHAR2 (1)
);
더미 데이터 생성
INSERT INTO DUAL_10
SELECT 'X'
FROM DUAL CONNECT BY LEVEL <= 10;
COMMIT;
ALTER TABLE TB_TRD NOLOGGING;
INSERT /*+ APPEND */ INTO TB_TRD -- APPEND 힌트 사용
SELECT LPAD(TO_CHAR(ROWNUM), 15, '0'),
TO_CHAR(SYSDATE - TRUNC(DBMS_RANDOM.VALUE(1, 365)), 'YYYYMMDD'),
TRUNC(DBMS_RANDOM.VALUE(1000, 100000)),
LPAD(TO_CHAR(TRUNC(DBMS_RANDOM.VALUE(0, 100000))), 10, '0'),
LPAD(TO_CHAR(TRUNC(DBMS_RANDOM.VALUE(0, 10000))), 6, '0'),
'DBMSEXPERT',
SYSDATE
FROM DUAL_10,
(SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 1000000);
COMMIT;
제약조건 생성
ALTER TABLE TB_TRD
ADD CONSTRAINT TB_TRD_PK
PRIMARY KEY (TRD_NO);
통계정보 생성
ANALYZE TABLE TB_TRD COMPUTE STATISTICS
FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS SIZE 254;
파티션 프루닝 튜닝 전
SELECT *
FROM TB_TRD
WHERE TRD_DT
BETWEEN TO_DATE(TO_CHAR(SYSDATE - 180, 'YYYYMMDD'), 'YYYYMMDD')
AND TO_DATE(TO_CHAR(SYSDATE - 120, 'YYYYMMDD'), 'YYYYMMDD');
SQL 분석
현재 시점으로부터 180일 전부터 120일 전까지의 YYYYMMDD를 TO_DATE 함수를 이용하여 DATE 타입으로 형 변환하여 조건을 주었다.
SQL 문제점
TRD_DT 컬럼의 데이터 타입은 VARCHAR2인데, TO_DATE 함수를 사용함으로써 VARCHAR2와 DATE의 비교 연산이 되었다.
오라클은 이럴 경우 DATE 타입에 우선권을 주게 되므로 TRD_DT 컬럼을 DATE 타입으로 묵시적 형 변환을 하게 된다.
묵시적 형 변환으로 인해 파티션 프루닝이 되지 않고, 불필요한 파티션까지 모두 스캔하게 되므로 성능이 안 좋은 SQL이다.
파티션 프루닝 튜닝 후
SELECT *
FROM TB_TRD
WHERE TRD_DT
BETWEEN TO_CHAR(SYSDATE - 180, 'YYYYMMDD')
AND TO_CHAR(SYSDATE - 120, 'YYYYMMDD');
SQL 분석
TRD_DT 컬럼의 데이터 타입은 VARCHAR2이므로 문자열 상수로 범위 조건을 주엇다.
같은 데이터 타입 간의 비교 연산이 이루어지므로 묵시적 형 변환이 이루어지지 않게 되었으며 파티션 프루닝이 가능해졌다.
