In-depth understanding of Oracle partitions (3): Partition Table and partition index Overview

(I) Partition Table technology Overview


(1) range partitioning



① Example



Create Table t

(... Column definition ...)

Partition by range (week_num)

(Partition P1 values less than (4) tablespace data0,

Partition P2 values less than (5) tablespace data1,

....

);



② Applicable scenarios



Range partitions are generally suitable for storing data by time period.



③ Advantages



The user knows which partition the specific data falls.

Therefore, you can effectively implement various mass data management operations through partitioning.

For example, you can delete historical data management for a specified period of time, back up and restore a specified partition, or import and export data.



④ Disadvantages



● Partition data may be uneven

● The range partition is related to the record value, and the implementation difficulty and maintainability are relatively poor.







(2) List Partition



① Example



Create Table t

(... Column definition ...)

Partition by list (city)

(Partition P1 values ('beijing') tablespace data0,

Partition P2 values ('shanghai') tablespace data1,

....

);



The difference between range and list is that the former is continuous, while the latter is discrete.

Therefore, the advantages and disadvantages and applicable scenarios are almost identical.

I will not repeat it here





(3) hash Partition



① Example



Create Table t

(... Column definition ..)

Partition by hash (customer_no)

Partition 8 store in

(Data0, data1 );



Reminder: Oracle recommends that the number of hash partitions is generally a power of 2.



② Applicable scenarios



Hash partitions are suitable for static data.

What is static data?

This type of data is usually stored in the database forever and does not require historical data migration.

For example, user data tables and account information

The access to such information is mostly carried out through the user ID or account.

If you perform hash Partitioning Based on these fields and create a local prefix partition index, the access efficiency is quite high.



③ Advantages



● Even Data Distribution

● Simple implementation



④ Disadvantages



The user does not know which partition a record will belong.

Therefore, hash partitions are not suitable for batch data management operations.

For example, historical data cleaning, import and export of large batches of data





(4) combined partitioning



Oracle combined partitioning is a concentrated manifestation of its advantages to some extent.

For example, in most cases, the first dimension is partitioned by time field,

In this way, it is suitable for managing mass data at the partition level.

The two-dimensional hash or list can further improve the access performance or reduce the implementation difficulty.



Before 11g, only: range-hash or range-list








(Ii) Partition Index Technology Overview



In the production environment, we sometimes encounter:

Partition tables have already been created. Why is the performance not improved? Even slower?

One of the reasons is that the partition index is not reasonable or even not designed.

First, let's take a general look at the relationship between the table and the index on the partition:

 

 

 

(1) local prefix partition Index

Assume that the partition table is a transaction flow meter T and is partitioned by the transaction date range.
If you want to create an index on the date field, we can:
Create index idx_t on T (date) local;
Idx_t is called a local prefix index.
The local and index partitioning method is the same as that of the corresponding table.
The prefix indicates that the partition field is the prefix of the index field.

Advantages
● Improved query performance
● When a partition is drop or merge, Oracle automatically performs the same operation on the corresponding index partition,
The entire local prefix partition index is still valid and does not require rebuild
This greatly guarantees the table availability.



(2) local non-Prefix partition Index

Suppose we need to create a partition index in the Area Field of the T table. We can:
Create index idx_t_area on T (area) local;
Idx_t_area is called a local Non-Prefix partition index.

Non-Prefix scan of all partitions by index, performance may be lower
However, it can ensure the availability of access by index

Applicable scenarios:
If the historical data is frequently organized and cannot be subject to the index unavailability caused by the reconstruction of global partition indexes for a long time
At the same time, the daily transaction performance is acceptable, it is recommended to design a local Non-Prefix partition Index



(3) global partition Index

Suppose we need to create a partition index in the Area Field of the T table. We can:
Create index idx_t_g_area on T (area)
Global partition by range (area)
(Partition P1 less ...);
Global means that the partition of the index is irrelevant to the partition of the table.
After 10 Gb, Oracle provides two global partition indexes:
● Global range partition Index
● Global hash partition Index

Disadvantages:
Mainly reflected in the high availability of data
When drop partition, global partition index is all invalid, unless rebulid
However, the larger the data volume, the longer the index reconstruction time.