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

    在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_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_big_table;end;　　使用非分区表执行5次的执行时间分别为：141.954s，89.656s，77.906s，98.5s，75.906s.　　由此可见采用有效的HASH分区表可以有效提升分析函数在oracle中的执行效率。我相信随着数据量的增加，将会有更明显的效果，回头再测试一个项目中遇到的类似问题

投稿作者：孤芳不自赏46