Oracle uses hash partition optimization analysis function Query

The analysis functions in ORACLE divide computing Windows based on several fields, and then aggregate, rank, and so on in the window. I think if the hash partition field of our data table is consistent with the partition by field in the analysis function, it can greatly speed up the analysis function operation efficiency. Because the data on each partition can be calculated separately. Do not interfere with each other. The experiment below will verify my thoughts.

Step 1: Create a partition table and a common table with the same structure as 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)
);

Step 2: Prepare the data and insert the data from dba_object to two tables. Insert a total of 1610880 data records.

insert into t_partition_hash select * from dba_objects; insert into t_partition_hash select * from dba_objects;

Step 3: Use the RANK function to query two tables.

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;

The running time of a hash Partition Table is 46.156 s, 33.39 s, 40.516 s 34.875 s, and 38.938 s, respectively.

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; The execution time of five non-partition tables is 141.954 s, 89.656 s, 77.906 s, 98.5 s, and 75.906 s, respectively.

It can be seen that using an effective HASH Partition Table can effectively improve the execution efficiency of analysis functions in oracle. I believe that as the amount of data increases, it will be more effective. I will test the similar problems encountered in a project.

1. Optimize Oracle tablespace design to improve database performance
2. Database optimization greatly improves Oracle Performance
3. Oracle sets system parameters for Performance Optimization