Necessity of rebuilding Oracle Indexes

Index rebuilding is a topic that is constantly under discussion. Of course, Oracle also has its own opinions. Many of our DBAs follow this rule to rebuild indexes, that is, we recommend that you re-create an index when the index depth exceeds 4 and the number of deleted index entries occupies at least 20% of the total number of existing index entries. Recently, Oracle has also put forward some opposite ideas, that is, it is strongly recommended not to rebuild indexes on a regular basis. This article is based on 1525787.1 and describes it accordingly.

1. Reasons for re-Indexing

2. Rebuilding the nature of Indexes

3. Reasons against re-Indexing

Although this is an effective index check method, it obtains the exclusive table lock when analyzing the index. For large indexes, the impact is huge because DML operations on tables are not allowed during this period.

B. The direct result of re-indexing is that the REDO activity may increase and the overall system load may increase.

The insert, update, and delete operations will lead to index growth along with the segmentation and growth of the index.

4. Final Oracle suggestions

6. Real re-Indexing

7. References

Oracle Index Quality Analysis