Partition of the study table

Source: Internet
Author: User

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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.