[Oracle][Performance]善用Partition Table#測試

2009-10-20

13175
0
Oracle
2009-11-04

由於小弟沒太多時間一一測試每種Partition Table，但我想只要依需求使用正確類型的Partition Table

就一定能感受到Partition Table所帶來的效能改善，當然如果用錯類型的話

那效能可就會比沒有切partition來的還糟糕。

這篇簡單測試Range Partition

1.建立Range Partiton

create table RANGE_BILL
(
BILL_NO VARCHAR2(13) not null,
BILL_TYPE VARCHAR2(1) default '1',
POLICY_NO VARCHAR2(10) not null,
BILL_STATUS_CODE VARCHAR2(1) default 'O',
CANCEL_REASON VARCHAR2(3),
CANCEL_REASON_1 VARCHAR2(1),

.......)

PARTITION BY RANGE (DUE_DATE_START)
( PARTITION BILL_p1_01 VALUES LESS THAN (TO_DATE('2008/1/01','yyyy/MM/dd'))
TABLESPACE DATA_BILL,
PARTITION BILL_p2_02 VALUES LESS THAN (TO_DATE('2008/2/01','yyyy/MM/dd'))
TABLESPACE DATA_BILL,
PARTITION BILL_p3_03 VALUES LESS THAN (TO_DATE('2008/3/01','yyyy/MM/dd'))
TABLESPACE DATA_BILL,
PARTITION BILL_p4_04 VALUES LESS THAN (TO_DATE('2008/4/01','yyyy/MM/dd'))
TABLESPACE DATA_BILL,
PARTITION BILL_p5_05 VALUES LESS THAN (TO_DATE('2008/5/01','yyyy/MM/dd'))
TABLESPACE DATA_BILL,
PARTITION BILL_p6_06 VALUES LESS THAN (TO_DATE('2008/6/01','yyyy/MM/dd'))
TABLESPACE DATA_BILL,
PARTITION BILL_p7_07 VALUES LESS THAN (TO_DATE('2008/7/01','yyyy/MM/dd'))
TABLESPACE DATA_BILL,
PARTITION BILL_p8_08 VALUES LESS THAN (TO_DATE('2008/8/01','yyyy/MM/dd'))
TABLESPACE DATA_BILL,
PARTITION BILL_p9_09 VALUES LESS THAN (TO_DATE('2008/9/01','yyyy/MM/dd'))
TABLESPACE DATA_BILL,
PARTITION BILL_p10_10 VALUES LESS THAN (TO_DATE('2008/10/01','yyyy/MM/dd'))
TABLESPACE DATA_BILL,
PARTITION BILL_p11_11 VALUES LESS THAN (TO_DATE('2008/11/01','yyyy/MM/dd'))
TABLESPACE DATA_BILL,
PARTITION BILL_p12_12 VALUES LESS THAN (TO_DATE('2008/12/01','yyyy/MM/dd'))
TABLESPACE DATA_BILL,
PARTITION BILL_others VALUES LESS THAN (MAXVALUE))
enable row movement;