我想只要依需求使用正確類型的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;

2.塞入資料

確認類型

Table筆數:

測試查詢效能

相同的sql statement但使用range partition

總表

依結果來看，使用partiton table大量降低IO，明顯提高效能，
但還是老話一句水能載舟亦能覆舟
請依需求正確使用，方能藥到病除，增強資料庫(台詞亂熟悉的XD)。