Go The need for Oracle to rebuild indexes

http://blog.csdn.net/leshami/article/details/23763963

Index reconstruction is an issue that has been hotly debated. Of course, Oracle has its own views, and many of our DBAs follow this guideline to rebuild the index, which is that Oracle recommends rebuilding the index in 2 scenarios where the index depth exceeds level 4 and the deleted index entry occupies at least 20% of the total number of existing index entries. Recently, Oracle has also put forward some of the opposite views, it is strongly recommended not to periodically rebuild the index. This article is referenced in 1525787.1 and is described accordingly.

1. Reasons for Rebuilding the index

A, the B-Tree index of Oracle becomes unbalanced over time (misunderstanding)

B, index fragmentation is increasing

C, the index is increasing, the deleted space is not reused

D, index clustering factor (cluster factor) out of sync, can be repaired by reconstruction (misunderstanding)

2. The nature of re-indexing

Essence: Rebuilding an index inside the database, the delete operation is performed before the insert operation.

3. Reasons against Rebuilding the index

A. Most scripts rely on index_stats dynamic tables.

This table is populated with the following command: Analyze index ... validate structure;

Although this is a valid index-checking method, it obtains exclusive table locks when it parses the index.        For large indexes, the impact is enormous, because DML operations on the table are not allowed during this period. Although this method can be run online without locking the table, it may take extra time.

B. The direct result of rebuilding the index is that the REDO activity may increase and the overall system load may increase.

The Insert/update/delete operation causes the index to evolve as the index splits and grows.        After rebuilding the index, it will connect more tightly, but as you continue to perform DML operations on the table, you must split the index again until the index is balanced.        As a result, the redo activity increases, and the index segmentation is more likely to have a direct impact on performance because we need to use more I/O, CPU, and so on for index rebuilds.        After a period of time, the index may encounter a "problem" again, and therefore may be marked as rebuilt, thus falling into a vicious circle. Therefore, it is usually better to have the index in a natural balance and/or at least prevent the index from being rebuilt periodically.

4. Oracle's final recommendations

In general, it is very rare to rebuild a B-tree index, basically because B-tree indexes can be largely self-managing or self-balancing.     Most indexes are balanced and complete, because the idle leaf entries can be reused. Insert/Update and DELETE operations do cause fragmentation of the free space around the index block, but in general these fragments are reused correctly.

The clustering factor cluster factor reflects the sorting of data in the table corresponding to the given index key value.     Rebuilding an index does not affect cluster factors, and cluster factors can only be changed by reorganizing the table's data.     It is strongly recommended that you do not rebuild indexes on a regular basis, but should use the appropriate diagnostic tools. Personal conclusion, if the huge amount of work to rebuild the index corresponds to a very small gain, it is not worth the candle. If the system has idle periods available, the measurements before and after the rebuild indicate improved performance and are worth rebuilding.

5, the improvement method

The index coalesce (index merge) is usually preferred, rather than rebuilding the indexes. Index merging has the following advantages:

A, do not need to occupy nearly twice times the space of disk storage space

B, can be operated online

c, instead of rebuilding the index structure, merge the index leaf blocks as quickly as possible to avoid excessive overhead.

6, the real need to rebuild the index of the situation

Index or index partition damaged due to media failure

Indexes marked as Unusabel need to be rebuilt

The index moves to a new table space or needs to change some storage parameters

You need to rebuild the index partition after loading the data to the table partition via Sql*loader

Rebuilding indexes to enable key compression

Bitmap indexing is fundamentally different from the B-tree index, which suggests rebuilding

7. Related references

Oracle clustering factor (clustering factor)

Oracle index Monitoring (monitor index)

Oracle index monitoring and foreign key indexing

Collecting statistics causes indexes to be monitored

Oracle Monitoring Index Utilization

NULL Value and index (i)

NULL value and Index (ii)

function invalidates indexed columns

Oracle Index Quality Analysis

Go The need for Oracle to rebuild indexes