The index and index of the partition technology

The index and index of the 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

How to create an optimal index using Oracle Indexing Technology

How to create the best index? 1. create index index_name on table_name (field_name) tablespace tablespace_namepctfree 5 initrans 2 maxtrans 255 storage (minextents 1 maxextents 16382 pctincrease 0 ); 2. create a function-based index that is commonly used in function categories such as UPPER, LOWER, and TO_CHAR (date). For example, create index idx_func on emp (UPPER (ename) tablespace tablespace_name; 3. When creating a bitmap index with a small base and a stable base, you should first consider bitmap indexes. For example, create bitmap index idx_bitm on class (classno) tablespace tablespace_nam E; 4. You can use the create unique index statement to create a unique index. For example, create unique index dept_unique_idx on dept (dept_no) tablespace idx_1; 5. You can use the using index statement to create a constraint-related index. It is an index related to the unique and primary key constraints. For example, alter table table_nameadd constraint PK_primary_keyname primary key (field_name) using index tablespace tablespace_name; how to create a local area index? 1) The basic table must be a partition table. 2) the number of partitions is the same as that of the basic table. 3) the number of subpartitions in each index partition is the same as that in the corresponding basic table partition. 4) the index items of rows in the Self-partition of the base table are stored in the corresponding self-partition of the index, for example, create index TG_CDR04_SERV_ID_IDX on TG_CDR04 (SERV_ID) pctfree 5 Tablespace TBS_AK01_IDXStorage (MaxExtents 32768 PctIncrease 0 FreeLists 1 FreeList Groups 1) local/how to create a global index for a range partition? Basic Tables can be global tables and partition tables create index idx_start_date on tg_cdr01 (start_date) global partition by range (start_date) (partition p01_idx vlaues less than ('20140901 ') partition p01_idx vlaues less than ('20140901 ')... partition p01_idx vlaues less than ('20140901')/How to recreate an existing index? Rebuilding an existing index does not affect the current time of the query. You can delete additional data blocks to improve the index query efficiency. alter index idx_name rebuild nologging; for the partition index alter index idx_name rebuild partition partition_name nologging; why is the index deleted? 1) indexes that are no longer needed 2) The index does not provide the expected performance improvement for queries published on the relevant tables 3) the application does not use the index to query data 4) The index is invalid, the index must be deleted before reconstruction. 5) the index has become too broken and must be duplicated ...... remaining full text>

Why re-create a partition index (index partition) in oracle )? What is the difference between rebuilding a partition index and an index partition?

1. The preceding statement re-creates an index for a single partition.
2. Index partitioning means creating a partition index instead of a common index.
3. re-create the partition index. For example, if there are too many shards and you want to change the tablespace, you can re-create it.
4. If the execution is complete, the execution is successful. The index partition information can be queried in user_ind_partitions.