在ORACLE中的分析函數都是基于某幾個字段劃分計算視窗，然後在視窗内進行聚合，排名，等等計算。我想如果我們資料表的hash分區字段與分析函數中的partition by 字段一緻的時候，應該可以大大加快分析函數的運作效率。因為每個分區上的資料可以單獨進行運算。互不幹涉，下面試驗來驗證我的想法.
***步：建立一個分區表和普通表，表結構與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)
);
第二步：準備資料，從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中的執行效率。我相信随着資料量的增加，将會有更明顯的效果，回頭再測試一個項目中遇到的類似問題。
【編輯推薦】
【責任編輯：彭凡 TEL：(010)68476606】
點贊 0