Oracle如何使用hash分区
第一步:创建一个分区表和普通表,表结构与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 varchar...全部
第一步:创建一个分区表和普通表,表结构与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中的执行效率。我相信随着数据量的增加,将会有更明显的效果,回头再测试一个项目中遇到的类似问题。
收起