Index of partition technology

Partition series:

Oracle Partitioning technology-first understanding http://blog.csdn.net/wanghui5767260/article/details/39158873

Index on Partitioning technology: http://blog.csdn.net/wanghui5767260/article/details/39181027

About Partitioning technology-Index

 

I. Partition Index classification:

Local prefix partition Index)

Global partition Index)

Local non-Prefix partition index (localnon-prefixed partitioned index)

 

1.1 combination of table and index:

 

First, neither the table nor the index is partitioned.

The simplest method is the common index.

 

Second, the table is partitioned, but the index is not partitioned.

This leads to: "We have already made a partition table. Why is the performance not improved ?" One of the main reasons.

In many systems, especially transaction systems, the database is accessed through indexes. If the index is not partitioned, the height of the index tree is not changed, so the access performance is certainly not improved. If you access a table by index, it has little to do with whether the table is partitioned.

 

Third, the table has no partitions, but the index is partitioned.

An index can only be a global partition index.

 

Category 4: Table Partitioning and index partitioning (important !!!)

Global partition Index

Local prefix partition Index

 

II. Introduction to partition Indexes

 

Local partition Index

The partitioning method of the index is the same as that of the corresponding table.

 

2.1 local prefix partition Index)

The partition field is the prefix of the index field.

A transaction flow meter (txn_current) is partitioned by year by the transaction date field (txn_date. Create an index in the txn_date field.

 

Createindex idx_txn_current_1 on txn_current (txn_date) local;

 

Or composite partition Index

Createindex idx_txn_current_2 on txn_current (txn_date, Area) local;

 

Benefits:

A. because the partition index corresponds to the table partition, the query data is directly queried by the corresponding index partition. The height of the index tree must be lower than that of the non-partition big index tree, that is to say, the performance is higher.

B. after a partition is deleted or merged, Oracle automatically performs the same operation on the corresponding index partition. The entire local prefix index is still valid, the rebuild operation is not required, which greatly guarantees the table availability.

 

2.2 Global partition Index)

The index partition is irrelevant to the table partition.

 

Partitioned Tables are partitioned by year, and partition indexes are created on the area field,

 

 

 

That is to say: Hangzhou transaction data can certainly be distributed in various years, as are Huzhou and Jiaxing transaction data.

If you have the following query requirements:

Select * From txn_current where area = '20140901 ';

-- Assume that you want to query Hangzhou Xihu District

 

Benefits:

A. At this time, Oracle will be smart enough to know that the index tree in Hangzhou is searched. The index height must be lower than that in non-partitioned conditions, which means the performance is higher.

B. When the partition granularity is relatively small, the performance is even higher than that of the local prefix partition index.

 

Disadvantages:

A. It is mainly reflected in high availability. If the table's data is deleted by partition in the past 03 years. The global partition indexes (including general non-partition indexes) are all invalid (invalid). These indexes are unavailable unless rebuild is performed. The larger the data volume, the larger the index volume, the longer the index reconstruction time, and the longer the time it takes to access data through such indexes. Therefore, data accessibility is greatly reduced.

 

2.3 local non-Prefix partition Index

This may cause performance degradation. The performance of local non-Prefix partition indexes may not be as good as that of non-partition indexes.

 

Advantages:

A. Improve access availability by index! We assume that we want to use the partition deletion technology to clean up the data for the past 03 years. If the area field index is set to a common index or a global partition index, we will face a problem: delete a partition (drop) after the operation, the normal index and global partition index will be invalid (invalid) and must be rebuilt. The advantage of a local non-Prefix partition index is that after the partition is deleted, the local Non-Prefix partition index is still valid.

 

3. Sort out ideas

 

Understanding the inventory map of partition indexes:

 

 

Description: treasure map: Find a treasure

(1) If the table partition field is an index field or its prefix. For example, if the partition field of the txn_current table is txn_date, txn_date is the prefix of the index field (txn_date, area, in this case, the local profixedpartitioned index should be created.

(2) Otherwise, if you want to create a non-partition field as a unique index, for example, to create a unique index for a field in the txn_current table, Oracle requires the Global prefixed index.

Otherwise, an error is returned:

ORA-14039: partitioning columns must form a subset of key columns of a unique index

 

(3) When the flow chart goes down, you need to determine whether the performance is within the tolerable range, and the management and availability of the partition are more important ?". If yes, the local Non-profixed index should be created. That is to say, as described above: If the historical data is frequently organized and cannot withstand the long-term unavailability of indexes caused by global partition index reconstruction, and the daily transaction performance is acceptable, we recommend that you design a local Non-Prefix partition index.

(4) The flowchart goes down and finally determines whether the system is a transaction system or a data warehouse system. Generally, the Data Warehouse has frequent mass data import (ETL) operations and historical data cleanup operations. At this time, the availability of the partition index is more important. Therefore, we recommend that you design it as localnon-profixed index. In the transaction system, the daily query performance is demanding and the historical data cleanup frequency is relatively low. Therefore, we recommend that you design the global profixed index.

Note: Oracle does not have the global non-profixed index concept.

 

4. Suggestions for Partition Table Design: more practical information !!!

 

(1) table size: when the table size exceeds 10 million GB, or when there are more than records in the OLTP system, partition the table.

(2) Data Access features: Most query applications based on tables only access a small amount of data in the table. When such a table is partitioned, the partition technology can be fully used to exclude the features of irrelevant data queries.

(3) Data Maintenance: delete batch data by time period, for example, delete historical data by month. Partitions must be considered for such tables to meet maintenance requirements.

(4) data backup and recovery: During table space backup by time period, a correspondence relationship is established between the partition and the table space.

(5) read-only data: if most of the data in a table is read-only data, you can partition the table and store the read-only data in the read-only tablespace, which is very helpful for database backup.

(6) parallel data operations: partitions should be considered for tables that frequently execute parallel operations (such as parallelinsert and parallel update.

(7) Table availability: When the availability requirements of some data in the table are very high, Table Partitioning should be considered.

2014/9/10 Tyger

This article is based on: Luo Min pinwu Performance Optimization

Index of partition technology