原理：数据表的hash分区字段与分析函数中的partition by 字段一致的时候，每个分区上的数据可以单独进行运算，互不干涉。所以可以很快的提高Oracle分析函数的运行效率。具体测试步骤如下：

***步：创建一个分区表和普通表，表结构与DBA_OBJECTS一致：

createtablet_partition_hash( 
object_namevarchar2(128), subobject_namevarchar2(30), 
object_idnumber, data_object_idnumber, 
object_typevarchar2(19), createddate, 
last_ddl_timedate, timestampvarchar2(19), 
statusvarchar2(7), temporaryvarchar2(1), 
generatedvarchar2(1), secondaryvarchar2(1) 
) partitionbyhash(object_type)( 
partitiont_hash_p1tablespaceUSERS, partitiont_hash_p2tablespaceUSERS, 
partitiont_hash_p3tablespaceUSERS, partitiont_hash_p4tablespaceUSERS, 
partitiont_hash_p5tablespaceUSERS, partitiont_hash_p6tablespaceUSERS, 
partitiont_hash_p7tablespaceUSERS, partitiont_hash_p8tablespaceUSERS 
); createtablet_big_hash( 
object_namevarchar2(128), subobject_namevarchar2(30), 
object_idnumber, data_object_idnumber, 
object_typevarchar2(19), createddate, 
last_ddl_timedate, timestampvarchar2(19), 
statusvarchar2(7), temporaryvarchar2(1), 
generatedvarchar2(1), secondaryvarchar2(1) 
); 

#p#

第二步：准备数据，从dba_object中把数据插入到两个表。总共插入数据1610880。

insertintot_partition_hashselect*fromdba_objects; 
insertintot_partition_hashselect*fromdba_objects; 

第三步：本采用RANK函数对两个表进行查询。

begin 
insertintot_rank selectobject_id, 
rank()over(partitionbyobject_typeorderbyobject_id)r_object_id, rank()over(partitionbyobject_typeorderbysubobject_name)r_subobject_name, 
rank()over(partitionbyobject_typeorderbycreated)r_created, rank()over(partitionbyobject_typeorderbylast_ddl_time)r_last_ddl_time, 
rank()over(partitionbyobject_typeorderbystatus)r_object_type fromt_partition_hash; 
end; 

使用hash分区表总共执行5次的运行时间分别为：46.156s,33.39s,40.516s 34.875s 38.938s.

begin 
insertintot_rank selectobject_id, 
rank()over(partitionbyobject_typeorderbyobject_id)r_object_id, rank()over(partitionbyobject_typeorderbysubobject_name)r_subobject_name, 
rank()over(partitionbyobject_typeorderbycreated)r_created, rank()over(partitionbyobject_typeorderbylast_ddl_time)r_last_ddl_time, 
rank()over(partitionbyobject_typeorderbystatus)r_object_type fromt_big_table; 
end; 

使用非分区表执行5次的执行时间分别为：141.954s，89.656s，77.906s，98.5s，75.906s.

由此可见采用有效的HASH分区表可以有效提升Oracle分析函数中的执行效率。我相信随着数据量的增加，将会有更明显的效果，回头再测试一个项目中遇到的类似问题。

善用Oracle表空间设计提升数据库性能

优化数据库大幅度提高Oracle分析函数的性能

Oracle设置系统参数进行性能优化