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

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

由於小弟沒太多時間一一測試每種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;

 

2.塞入資料

image

image

確認類型

image

Table筆數:

image

 

測試查詢效能

image

image

image

相同的sql statement但使用range partition

image

image

image

總表

image

依結果來看,使用partiton table大量降低IO,明顯提高效能,但還是老話一句水能載舟亦能覆舟

請依需求正確使用,方能藥到病除,增強資料庫(台詞亂熟悉的XD)。