Summary of Oracle debate on re-indexing, oracle re-Indexing

Summary of Oracle debate on re-indexing, oracle re-Indexing

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

A. Oracle B-tree indexes become unbalanced over time (misunderstanding)
B. Increasing index fragmentation
C. The index is increasing, and the deleted space is not reused.
D. The index clustering factor (cluster factor) is not synchronized and can be restored by reconstruction (misunderstanding)

2. Rebuilding the nature of Indexes

Essence: Re-indexing is performed inside the database before the deletion operation.

3. Reasons against re-Indexing

A. Most scripts depend on the index_stats dynamic table. This table is filled with the following command:
Analyze index... validate structure;
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.
Although this method can run online without locking the table, it may take additional time.
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.
After the index is re-built, it will be more compact; however, as the table continues to perform DML operations, the index must be split again until the index reaches a balance.
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 and CPU for index reconstruction.
After a period of time, the index may encounter a "problem" again, so it may be marked as a reconstruction, which leads to a vicious circle.
Therefore, it is usually better to put the index in a natural balance and (or) at least prevent regular index reconstruction.

4. Final Oracle suggestions

In general, there is very little need to re-build the B-tree index, basically because the B-tree index can be self-managed or self-balanced to a large extent.
Most indexes are balanced and complete, Because idle leaf entries can be reused.
The insert, update, and delete operations will indeed cause fragments of the available space around the index block, but these fragments will be correctly reused.
The Clustering factor cluster factor reflects the data sorting in the table corresponding to the given index key value. Re-indexing does not affect cluster factors. Cluster factors can only be changed by reorganizing the table data.
We strongly recommend that you use appropriate diagnostic tools instead of rebuilding indexes on a regular basis.
My personal conclusion is that if the huge workload of re-indexing corresponds to a very small benefit, the loss will be worth the candle. If the system has an available idle period, the measurement results before and after reconstruction indicate that the performance is improved and it is worth rebuilding.

5. Improvement Methods

Index coalesce (index merging) is usually given priority, rather than re-indexing. Index merging has the following advantages:
A. There is no need to occupy nearly twice the disk storage space
B. online operations
C. Merge the index leaf blocks as soon as possible instead of rebuilding the index structure to avoid excessive system overhead.

6. Real re-Indexing

Index or index partition damaged due to media failure
Indexes marked as UNUSABEL need to be rebuilt.
Moving an index to a new tablespace or changing some storage Parameters
After loading data to table partitions through SQL * Loader, you need to re-create the index partition.
Re-indexing to enable key Compression
Bitmap indexes are essentially different from B-tree indexes. Reconstruction is recommended.

Oracle re-Indexing

1. Collect statistics again
Analyze table name compute statistics for table for all indexes for all indexed columns
Analyze index name compute statistics
2. Send different SQL statements and their execution plans.

How to re-create an index in oracle?

Alter index ind_id_idx rebuild; -- re-create the index
Select if_rows, if_rows_len, del_id_rows_len from index_stats;
.....