When to rebuild the indexes

Knowing when to Rebuild Indexes:

There are two rules of thumb to help determine if the index needs to be rebuilt.

1)     If the index has height greater than four, rebuild the index.

2)     The deleted leaf rows (index nodes) should be less than 20%.

Index height

The height of the index refers to the number of levels that are spawned by the index as a result in row inserts. When a large amount of rows are added to a table, Oracle may spawn additional levels of an index to accommodate the new rows. Hence, an Oracle index may have four levels, but only in those areas of the index tree where the massive inserts have occurred. Oracle indexes can support many millions of entries in three levels, and any SAP index that has four or more levels would benefit from rebuilding.

The number of deleted leaf nodes

The term "deleted leaf node" refers to the number of index nodes that have been logically deleted as a result of row deletes. Remember that Oracle leaves "dead" index nodes in the index when rows are deleted. This is done to speed up SQL deletes, since Oracle does not have to rebalance the index tree when rows are deleted.

We must first get an idea of the current state of the index by using the ANALYZE INDEX VALIDATE STRUCTURE command.
The VALIDATE STRUCTURE command can be safely executed without affecting the optimizer. The VALIDATE STRUCTURE command populates the SYS.INDEX_STATS table only. The SYS.INDEX_STATS table can be accessed with the public synonym INDEX_STATS. The INDEX_STATS table will only hold validation information for one index at a time. You will need to query this table before validating the structure of the next index.
Below is a sample output from INDEX_STATS Table.

SQL> ANALYZE INDEX IDX_GAM_ACCT VALIDATE STRUCTURE;
 
Statement processed.
 
SQL> SELECT name, height,lf_rows,lf_blks,del_lf_rows FROM
INDEX_STATS;
NAME                      HEIGHT    LF_ROWS    LF_BLKS    DEL_LF_ROW
---------------------- -----------   ----------      ----------   ----------------
DX_GAM_ACCT           2             1                     3               6
 
1 row selected.
 

SQL> ANALYZE INDEX IDX_GAM_FID VALIDATE STRUCTURE;
 
Statement processed.
 
SQL> SELECT name, height, lf_rows, del_lf_rows, (del_lf_rows/lf_rows)
*100 as ratio FROM INDEX_STATS;
 
NAME                           HEIGHT     LF_ROWS    DEL_LF_ROW RATIO    
------------------------------ ---------- ---------- ---------- -------
IDX_GAM_FID                                  1          189         62        32.80
 
1 row selected.
 
In this example, the ratio of deleted leaf rows to total leaf rows
is clearly above 20%. This is a good candidate for rebuilding.
Let’s rebuild the index and examine the results
 
SQL> ANALYZE INDEX IDX_GAM_FID REBUILD;
 
Statement processed.
 
SQL> ANALYZE INDEX IDX_GAM_FID VALIDATE STRUCTURE;
 
Statement processed.
 
SQL> SELECT name, height, lf_rows, del_lf_rows, (del_lf_rows/lf_rows)*
100 as ratio FROM INDEX_STATS;
 
NAME                           HEIGHT     LF_ROWS    DEL_LF_ROW RATIO    
------------------------------ ---------- ---------- ---------- -------
IDX_GAM_FID                                  1          127         0        0
 
1 row selected.

Examining the INDEX_STATS table shows that the 62 deleted leaf rows were dropped from the index. Notice that the total number of leaf rows went from 189 to 127, which is a difference of 62 leaf rows (189-127). This index should provide better performance for the application.


REBUILD INDEXES:

Using ALTER INDEX REBUILD to Rebuild Indexes.

The ALTER INDEX index_name REBUILD command is very safe way to rebuild indexes. Here is the syntax of the command:

Unlike the traditional method where we drop the index and recreate it, the REBUILD command does not require a full table scan of the table, and the subsequent sorting of the keys and rowids. Rather, the REBUILD command will perform the following steps:

1. 
   
2. Walk the existing index to get the index keys.
3. Populate temporary segments with the new tree structure.
4. Once the operation has completed successfully, drop the old tree, and rename the temporary segments to the new index.