如何使用Hash分區優化Oracle分析函數

原理：數據表的hash分區字段與分析函數中的partition by 字段一致的時候，每個分區上的數據可以單獨進行運算，互不干涉。所以可以很快的提高Oracle分析函數的運行效率。具體測試步驟如下：

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

1. create table t_partition_hash(  
2. object_name varchar2(128),  
3. subobject_name varchar2(30),  
4. object_id number,  
5. data_object_id number,  
6. object_type varchar2(19),  
7. created date,  
8. last_ddl_time date,  
9. timestamp varchar2(19),  
10. status varchar2(7),  
11. temporary varchar2(1),  
12. generated varchar2(1),  
13. secondary varchar2(1)  
14. )  
15. partition by hash(object_type)(  
16. partition t_hash_p1 tablespace USERS,  
17. partition t_hash_p2 tablespace USERS,  
18. partition t_hash_p3 tablespace USERS,  
19. partition t_hash_p4 tablespace USERS,  
20. partition t_hash_p5 tablespace USERS,  
21. partition t_hash_p6 tablespace USERS,  
22. partition t_hash_p7 tablespace USERS,  
23. partition t_hash_p8 tablespace USERS  
24. );  
25. create table t_big_hash(  
26. object_name varchar2(128),  
27. subobject_name varchar2(30),  
28. object_id number,  
29. data_object_id number,  
30. object_type varchar2(19),  
31. created date,  
32. last_ddl_time date,  
33. timestamp varchar2(19),  
34. status varchar2(7),  
35. temporary varchar2(1),  
36. generated varchar2(1),  
37. secondary varchar2(1)  
38. );  
39.  

第二步：准備數據，從dba_object中把數據插入到兩個表。總共插入數據1610880。
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分區表總共執行5次的運行時間分別為：46.156s,33.39s,40.516s 34.875s 38.938s.
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;  
 
使用非分區表執行5次的執行時間分別為：141.954s，89.656s，77.906s，98.5s，75.906s.
由此可見采用有效的HASH分區表可以有效提升Oracle分析函數中的執行效率。我相信隨著數據量的增加，將會有更明顯的效果，回頭再測試一個項目中遇到的類似問題。
善用Oracle表空間設計提升數據庫性能
優化數據庫大幅度提高Oracle分析函數的性能
Oracle設置系統參數進行性能優化