Oracle使用hash分區優化分析函數查詢

　　在ORACLE中的分析函數都是基於某幾個字段劃分計算窗口然後在窗口內進行聚合排名等等計算我想如果我們數據表的hash分區字段與分析函數中的partition by 字段一致的時候應該可以大大加快分析函數的運行效率因為每個分區上的數據可以單獨進行運算互不干涉下面試驗來驗證我的想法

　　第一步創建一個分區表和普通表表結構與DBA_OBJECTS一致

　　

　　

　　create table t_partition_hash(
object_name varchar()
subobject_name varchar()
object_id number
data_object_id number
object_type varchar()
created date
last_ddl_time date
timestamp varchar()
status varchar()
temporary varchar()
generated varchar()
secondary varchar()
)
partition by hash(object_type)(
partition t_hash_p tablespace USERS
partition t_hash_p tablespace USERS
partition t_hash_p tablespace USERS
partition t_hash_p tablespace USERS
partition t_hash_p tablespace USERS
partition t_hash_p tablespace USERS
partition t_hash_p tablespace USERS
partition t_hash_p tablespace USERS
);

　　create table t_big_hash(
object_name varchar()
subobject_name varchar()
object_id number
data_object_id number
object_type varchar()
created date
last_ddl_time date
timestamp varchar()
status varchar()
temporary varchar()
generated varchar()
secondary varchar()
);

　　第二步准備數據從dba_object中把數據插入到兩個表總共插入數據

　　

　　insert into t_partition_hash select * from dba_objects;

　　insert into t_partition_hash select * from dba_objects;

　　第三步本采用RANK函數對兩個表進行查詢

　　

　　begin
insert into  t_rank
select object_id
rank() over (partition by object_type order by object_id)  r_object_id
rank() over (partition by object_type order by subobject_name) r_subobject_name
rank() over (partition by object_type order by created) r_created
rank() over (partition by object_type order by last_ddl_time) r_last_ddl_time
rank() over (partition by object_type order by status) r_object_type
from t_partition_hash;
end;

　　使用hash分區表總共執行次的運行時間分別為sss s s

　　

　　begin
insert into  t_rank
select object_id
rank() over (partition by object_type order by object_id)  r_object_id
rank() over (partition by object_type order by subobject_name) r_subobject_name
rank() over (partition by object_type order by created) r_created
rank() over (partition by object_type order by last_ddl_time) r_last_ddl_time
rank() over (partition by object_type order by status) r_object_type
from t_big_table;
end;

　　使用非分區表執行次的執行時間分別為sssss

　　由此可見采用有效的HASH分區表可以有效提升分析函數在oracle中的執行效率我相信隨著數據量的增加將會有更明顯的效果回頭再測試一個項目中遇到的類似問題

From:http://tw.wingwit.com/Article/program/Oracle/201311/17539.html