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

数据库 Oracle 数据库运维
本文将介绍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函数对两个表进行查询。

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

【编辑推荐】

  1. 善用Oracle表空间设计提升数据库性能
  2. 优化数据库大幅度提高Oracle的性能
  3. Oracle设置系统参数进行性能优化
责任编辑:彭凡 来源: ITPUB
相关推荐

2010-04-19 13:43:38

Oracle分析函数

2010-10-25 10:55:11

Oracle函数索引

2018-06-26 15:58:06

数据库MySQL索引优化

2017-03-09 13:11:48

Oracle分析函数

2009-06-03 10:32:36

Oracle性能优化分区技术

2021-01-24 11:46:26

自动化Web 优化

2010-04-20 13:30:42

2019-03-19 14:52:00

性能优化MySQL数据库

2021-08-07 05:00:20

存储系统

2010-04-22 12:46:55

Oracle hint

2017-09-05 09:02:06

Oraclenot null优化

2021-07-04 07:07:06

Ceph分布式存储架构

2010-04-28 16:45:27

Oracle Inst

2017-07-19 09:53:42

Oracle分区问题

2021-01-20 08:07:52

oracle分区单表

2018-04-28 16:20:31

机器学习算法分发链路

2023-07-26 12:38:42

PyGWalker数据类型

2010-10-25 10:20:33

ORACLE函数

2010-04-12 17:47:01

Oracle多表查询

2010-05-07 11:00:25

Oracle多表查询
点赞
收藏

51CTO技术栈公众号