Plsql_ Performance Optimization Series 15_oracle index rebuild indexes rebuild

2014-10-04 Baoxinjian

I. Summary

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

• Oracle's B-tree index becomes unbalanced over time (misunderstanding)
• Index fragmentation is increasing
• The index keeps increasing and the deleted space is not reused
• Index clustering factor (cluster factor) is out of sync and can be repaired (misunderstood) by reconstruction

2. Rebuilding the nature of the index

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

3. Reasons for opposing the rebuilding of the index

(1). Most scripts rely on index_stats dynamic tables. This table is populated with the following commands:

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.

(2). 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. Improvement methods

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

• No need to occupy nearly twice times the space of disk storage space
• can be operated online
• Instead of rebuilding the index structure, you can avoid excessive overhead by merging index leaf blocks as quickly as possible.

6. The real need to re-index 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

Second, the case

Index is a powerful weapon to improve database query performance.

Without an index, like a library without a book tag, finding a book you want is harder than it is.

However, in the process of using the index, especially in the case of batch DML, the corresponding fragmentation will occur, and the B-tree height will change accordingly, so that these changed indexes can be reconstructed to improve performance.

N long ago Oracle recommends that we periodically rebuild indexes on those tables where the height is 4, and the deleted index entries occupy at least 20% of the total number of existing index entries.

However, Oracle now strongly recommends against rebuilding indexes on a regular basis.

Reference: The Novices http://blog.csdn.net/leshami/article/details/23763963

Reference: The Novices http://blog.csdn.net/leshami/article/details/24266247

Plsql_ Performance Optimization Series 15_oracle index rebuild indexes rebuild