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

***步：創(chuàng)建一個分區(qū)表和普通表，表結(jié)構(gòu)與DBA_OBJECTS一致：

create table t_partition_hash(  
object_name varchar2(128),  
subobject_name varchar2(30),  
object_id number,  
data_object_id number,  
object_type varchar2(19),  
created date,  
last_ddl_time date,  
timestamp varchar2(19),  
status varchar2(7),  
temporary varchar2(1),  
generated varchar2(1),  
secondary varchar2(1)  
)  
partition by hash(object_type)(  
partition t_hash_p1 tablespace USERS,  
partition t_hash_p2 tablespace USERS,  
partition t_hash_p3 tablespace USERS,  
partition t_hash_p4 tablespace USERS,  
partition t_hash_p5 tablespace USERS,  
partition t_hash_p6 tablespace USERS,  
partition t_hash_p7 tablespace USERS,  
partition t_hash_p8 tablespace USERS  
);  
create table t_big_hash(  
object_name varchar2(128),  
subobject_name varchar2(30),  
object_id number,  
data_object_id number,  
object_type varchar2(19),  
created date,  
last_ddl_time date,  
timestamp varchar2(19),  
status varchar2(7),  
temporary varchar2(1),  
generated varchar2(1),  
secondary varchar2(1)  
);  
 

#p#

第二步：準(zhǔn)備數(shù)據(jù)，從dba_object中把數(shù)據(jù)插入到兩個表。總共插入數(shù)據(jù)1610880。

insert into t_partition_hash select * from dba_objects;  
insert into t_partition_hash select * from dba_objects;  
 

第三步：本采用RANK函數(shù)對兩個表進(jìn)行查詢。

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分區(qū)表總共執(zhí)行5次的運(yùn)行時間分別為：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;  
 

使用非分區(qū)表執(zhí)行5次的執(zhí)行時間分別為：141.954s，89.656s，77.906s，98.5s，75.906s.

由此可見采用有效的HASH分區(qū)表可以有效提升Oracle分析函數(shù)中的執(zhí)行效率。我相信隨著數(shù)據(jù)量的增加，將會有更明顯的效果，回頭再測試一個項目中遇到的類似問題。

善用Oracle表空間設(shè)計提升數(shù)據(jù)庫性能

優(yōu)化數(shù)據(jù)庫大幅度提高Oracle分析函數(shù)的性能

Oracle設(shè)置系統(tǒng)參數(shù)進(jìn)行性能優(yōu)化
 

【編輯推薦】

1. 修改Oracle存儲過程所需代碼
2. 對Oracle存儲過程的總結(jié)
3. 實(shí)現(xiàn)Oracle存儲過程的實(shí)際應(yīng)用的代碼 
4. 深入高性能的Oracle動態(tài)SQL開發(fā) 
5. Oracle SQL的優(yōu)化規(guī)則解析