Partition of the study table

I have been studying the knowledge of table partitions in Oracle for a while. As the data volume continues to expand, the Partitioning technology is a very effective technology to improve efficiency.

Due to business restrictions, I used to partition partitions by date every time I create a partition table, with a range partition every day. But suddenly one day I met a very annoying table, which is a relational table with only the correspondence between products and sales products, in this way, there is no way to create a range partition that I am most familiar with. After reading the information, I found that hash partition is a better solution.

A hash partition is used to perform hash operations on partition fields and evenly distribute data in each partition. Hash: I have been to the Data Structure course in college. It means that this method is perfect when I hear this name.

New partition statement:

 
Create TableTest PartitionByHash (Object_id) Partitions8As Select * FromDba_objects;
 

In this way, the system will automatically generate eight partitions, and the name of each partition is also specified by the system:

I really don't understand why I started to score points from 21. Let's talk about it later.

You can check the amount of data in each partition. The range partition is very good. You can check the data by partition column conditions. This hash is not good. I checked the statement as follows:

 
Select Count(1)FromTest partition (sys_p21 );
 

The data volume in these partitions is as follows:

The results are very good.

Next we will look at the data query efficiency. First, perform an analyze operation on the table. Then autotrace the execution plan as follows:

The experiment requires a control group. Create a table without partitions. The data is the same as test. The table is test_2, insert the data, and then analyze the data. Then, see the execution plan:

The results are very good, and the partitioning method of the messy table is simply awesome.