Oracle's summary of the controversy over rebuilding indexes _oracle

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 that you rebuild the index in the 2 cases where the index depth exceeds 4 and the deleted index entries occupy at least 20% of the total number of existing index entries. Oracle has also recently put forward a number of opposing views, that is, it is strongly recommended not to rebuild the index regularly. This article refers to 1525787.1 and describes it accordingly.

1, the reasons for rebuilding the index

A, Oracle's B-tree index becomes unbalanced over time (misunderstanding)
B, index fragmentation is increasing
C, the index continues to increase, the deleted space is not repeated use
D, index clustering factor (cluster factor) is not synchronized and can be repaired by rebuilding (misunderstanding)

2, the nature of the reconstruction index

Essence: Rebuilding the index inside the database is the first delete operation, and then 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 acquires an exclusive table lock when parsing an index. For large indexes, the impact is enormous because DML operations on tables are not allowed during this period.
Although this method can run online without locking the table, it may take an extra time.
B. The immediate 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 the index is rebuilt, it will be more compact; However, 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, CPUs, etc. for index reconstruction.
After a period of time, the index may again encounter "problems", so it may be marked for reconstruction, and thus into a vicious circle.
Therefore, it is usually best to keep indexes in a natural balance and/or at least prevent the indexing from being rebuilt periodically.

4, Oracle's final recommendations

In general, it is very rare to rebuild a B-tree index because the B-tree index is largely self-management or self balanced.
Most indexes are balanced and complete, because idle leaf entries can be reused.
Insert/Update and DELETE operations do cause fragmentation of free space around the index block, but generally these fragments are reused correctly.
The clustering factor cluster factor reflects the sort of data in the table corresponding to the given index key value. Rebuilding an index does not affect the cluster factor, and the cluster factor can only be changed by reorganizing the table's data.
It is strongly recommended that you do not rebuild the index periodically, but use appropriate diagnostic tools.
Personal conclusions, if the huge workload of rebuilding an index corresponds to a minimal gain, it is not worth the candle. If the system has available idle periods, the measurements before and after the reconstruction indicate that performance has improved and is worth rebuilding.

5. Improved method

The index coalesce (index merge) is usually preferred rather than rebuilt. Index consolidation has the following advantages:
A, do not need to occupy nearly twice times the space of disk storage space
b, can be online operation
c, you do not need to rebuild the index structure, but rather quickly merge the index leaf blocks, which avoids the overhead of the system.

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

    index or index partition is corrupted by media failure
    index marked as Unusabel need to be rebuilt
    Index moved to new tablespace or need to change some storage parameters
    after loading data to table partitions by Sql*loader, rebuild the index partition
    rebuild the index to enable key compression
    bitmap indexing is essentially different from the B-tree index, and it is recommended that you rebuild