Knowing when to Rebuild Indexes:
2) The deleted leaf rows (index nodes) should be less than 20%.
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.
REBUILD INDEXES:
The ALTER INDEX index_name REBUILD command is very safe way to rebuild indexes. Here is the syntax of the command:
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.
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:
- Walk the existing index to get the index keys.
- Populate temporary segments with the new tree structure.
- Once the operation has completed successfully, drop the old tree, and rename the temporary segments to the new index.
