partition pruning test

1. 파티션테이블 생성

CREATE TABLE TB_TRD
(
TRD_NO VARCHAR2(15),
TRD_DT VARCHAR2(8),
TRD_AMT NUMBER(15),
CUST_ID VARCHAR2(10),
PRDT_CD VARCHAR2(6),
INST_ID VARCHAR2(50),
INST_DTM DATE
)
PARTITION BY RANGE(TRD_DT)
(
PARTITION P_TB_TRD_201609 VALUES LESS THAN ('20160901'),
PARTITION P_TB_TRD_201610 VALUES LESS THAN ('20161001'),
PARTITION P_TB_TRD_201611 VALUES LESS THAN ('20161101'),
PARTITION P_TB_TRD_201612 VALUES LESS THAN ('20161201'),
PARTITION P_TB_TRD_201701 VALUES LESS THAN ('20170101'),
PARTITION P_TB_TRD_201702 VALUES LESS THAN ('20170201'),
PARTITION P_TB_TRD_201703 VALUES LESS THAN ('20170301'),
PARTITION P_TB_TRD_201704 VALUES LESS THAN ('20170401'),
PARTITION P_TB_TRD_201705 VALUES LESS THAN ('20170501'),
PARTITION P_TB_TRD_201706 VALUES LESS THAN ('20170601'),
PARTITION P_TB_TRD_201707 VALUES LESS THAN ('20170701'),
PARTITION P_TB_TRD_201708 VALUES LESS THAN ('20170801'),
PARTITION P_TB_TRD_201709 VALUES LESS THAN ('20170901'),
PARTITION P_TB_TRD_201710 VALUES LESS THAN ('20171001'),
PARTITION P_TB_TRD_MAX VALUES LESS THAN (MAXVALUE)
);

 

 

2.데이터 입력

ALTER TABLE TB_TRD NOLOGGING;

 

INSERT /*+ APPEND */ INTO TB_TRD
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 (SELECT 'X' FROM DUAL CONNECT BY LEVEL <=10) A,
     (SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <=1000000);
    
     COMMIT;

 

3.기본키 생성

ALTER TABLE TB_TRD ADD CONSTRAINT TB_TRD_PK PRIMARY KEY(TRD_NO);

 

4.통계정보 생성

ANALYZE TABLE TB_TRD COMPUTE STATISTICS FOR TABLE FOR ALL INDEXES ;

 

5. 튜닝전 SQL

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');

 

Execution Plan
-----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=28K Card=25K Bytes=2M)
   1    0   FILTER
   2    1     PARTITION RANGE (ALL) (Cost=28K Card=25K Bytes=2M)
   3    2       TABLE ACCESS (FULL) OF 'TB_TRD' (TABLE) (Cost=28K Card=25K Bytes=2M)
-----------------------------------------------------------

수행시간 : 약 3초

 

6. 튜닝후 SQL

SELECT *
FROM TB_TRD
WHERE TRD_DT BETWEEN TO_CHAR(SYSDATE-180,'YYYYMMDD')
             AND TO_CHAR(SYSDATE-120,'YYYYMMDD');

 

Execution Plan
-----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=28K Card=25K Bytes=2M)
   1    0   FILTER
   2    1     PARTITION RANGE (ITERATOR) (Cost=28K Card=25K Bytes=2M)
   3    2       TABLE ACCESS (FULL) OF 'TB_TRD' (TABLE) (Cost=28K Card=25K Bytes=2M)
-----------------------------------------------------------

수행시간 : 약 0.5초