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

核心提示：在ORACLE中的分析函数都是基于某几个字段划分计算窗口，然后在窗口内进行聚合，Oracle使用hash分区优化分析函数查询，排名，等等计算，我相信随着数据量的增加，将会有更明显的效果，我想如果我们数据表的hash分区字段与分析函数中的partition by 字段一致的时候，应该可以大大加快分析函数的运行效率

在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中的执行效率。我相信随着数据量的增加，将会有更明显的效果，回头再测试一个项目中遇到的类似问题。