原理：数据表的hash分区字段与分析函数中的partition by 字段一致的时候，每个分区上的数据可以单独进行运算，互不干涉。所以可以很快的提高Oracle分析函数的运行效率。具体测试步骤如下：
***步：创建一个分区表和普通表，表结构与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#
第二步：准备数据，从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设置系统参数进行性能优化
【编辑推荐】
