Oracle使用hash分区优化分析函数查询.docVIP

在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函数对两个表进行查询。 begininsert into? t_rankselect 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_typefrom t_partition_hash;end; 使用hash分区表总共执行5次的运行时间分别为：46.156s,33.39s,40.516s 34.875s 38.938s. begininsert into? t_rankselect 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_subobje