Rebuild the indexes

REBUILDING INDEXES

Indexes require rebuilding when deleted leaf rows appear or when the index has a suboptimal number of block gets per access. While it is tempting to write a script that rebuilds every index in the schema,

Oracle index nodes are not physically deleted when table rows are deleted, nor are the entries removed from the index. Rather, Oracle "logically" deletes the index entry and leaves "dead" nodes in the index tree where that may be re-used if another adjacent entry is required. However, when large numbers of adjacent rows are deleted, it is highly unlikely that Oracle will have an opportunity to re-use the deleted leaf rows, and these represent wasted space in the index.  In addition to wasting space, large volumes of deleted leaf nodes will make index fast-full scans run for longer periods.

-        Deleted entries represent 20% or more of the current entries.

-        The index depth is more than 4 levels.

SQL> ANALYZE INDEX index_name COMPUTE STATISTICS

SQL> ANALYZE INDEX index_name VALIDATE STRUCTURE

 

After you analyze the report above, you may want to consider rebuilding any index

We might want to rebuild an index if the “block gets” per access is greater than five, since excessive “blocks gets” indicate a fragmented b-tree structure. Another rebuild condition would be cases where deleted leaf nodes comprise more than 20% of the index nodes. As you may know, you can easily rebuild an Oracle index with the command:

ALTER INDEX index_name REBUILD

SQLWKS> ANALYZE INDEX item_basket_pk VALIDATE STRUCTURE;

Statement processed.

SQLWKS> 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

------------------------------                 ----------        ----------            ----------               ----------

ITEM_BASKET_PK                              1                      235                  74                 31.4893617

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.

SQLWKS> ALTER INDEX item_basket_pk REBUILD;

SQLWKS> ANALYZE INDEX item_basket_pk VALIDATE STRUCTURE;

SQLWKS> SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio

     2> FROM INDEX_STATS;

NAME                           HEIGHT     LF_ROWS    DEL_LF_ROW RATIO     

------------------------------ ---------- ---------- ---------- ----------

ITEM_BASKET_PK                          1        161          0          0

1 row selected.

The index is rebuilt and validated once again. Examining the INDEX_STATS table shows that the 74 deleted leaf rows were dropped from the index. Notice that the total number of leaf rows went from 235 to 161, which is a difference of 74 leaf rows. This index should provide better performance for the application.

 

Oracle provides an analyze index xxx validate structure command that provides additional statistics into a temporary table called index_stats. But, the information needs to be saved, as each analyze validate structure command overlays the information.

To get the full picture, you need both pieces. Also, there are certainly some columns that are more important than others:

• CLUSTERING_FACTOR. This is one of the most important index statistics because it indicates how well sequenced the index columns are to the table rows. If clustering_factor is low (about the same as the number of dba_segments.blocks in the table segment) than the index key is in the same order as the table rows and index range scan will be very efficient, with minimal disk I/O. As clustering_factor increases (up to dba_tables.num_rows), the index key is increasingly out of sequence with the table rows. Oracle's cost-based SQL optimizer relies heavily upon clustering_factor to decide whether or not to use the index to access the table.
• HEIGHT. As an index accepts new rows, the index blocks split. Once the index nodes have split to a predetermined maximum level the index will "spawn" into a new level.
• BLOCKS This is the number of blocks consumed by the index. This is dependent on the db_block_size. In Oracle9i and beyond, many DBAs create b-tree indexes in very large blocksizes (db_32k_block_size) because the index will spawn less. Robin Schumacher has noted in his book Oracle Performance Troubleshooting notes:

"As you can see, the amount of logical reads has been reduced in half simply by using the new 16K tablespace and accompanying 16K data cache. Clearly, the benefits of properly using the new data caches and multi-block tablespace feature of Oracle9i and above are worth your investigation and trials in your own database."

• PCT_USED. This metric is very misleading because it looks identical to the dba_indexes pct_used column, but has a different meaning. Normally, the pct_used threshold is the freelist unlink threshold, while in index_stats pct_used is the percentage of space allocated in the b-tree that is being used.

Are there criteria for index/table rebuilding?

The short answer is no, there is no 100% complete, definitive list. But, here are some things to start with:

• Index levels > 3
• Pct_used < 75%
• More than 20% of the rows have been deleted (space is not automatically reused)
• Index is becoming unclustered, and performance is degrading (causing increases in number of blocks to be read) – while unloading, resorting, and reloading the data in a table may provide better performance, this is an additional maintenance activity that needs to be performed, and can be difficult to keep the rows in their proper sequence, if the table gets a lot of insert/update/delete activity.