Oracle rebuild index and status

In tables where changes constantly, as a maintenance over the DB, you have to rebuild the indexes B-TREE periodically, for example (the syntax is more complex):

Source   
ALTER INDEX SCOTT.PK_EMP REBUILD;

A B-TREE ideal has branches all perfectly balanced, a B-TREE degraded can offer linear time rather than logarithmic search because of strong imbalance.

To determine if an index should be rebuilt:

Source   
ANALYZE INDEX SCOTT.PK_EMP VALIDATE STRUCTURE;
SELECT * FROM INDEX_STATS I WHERE i.del_lf_rows/i.lf_rows > 0.3 OR i.height>5 OR i.lf_rows<i.lf_blks AND i.name='PK_EMP';

B-tree-searchThis query returns the index has erased more than 30% of total values (i.del_lf_rows / i.lf_rows> 0.3), the depth of the B-tree is greater than 4 (i.height> 5) or the number block is greater than the number of values (i.lf_rows <i.lf_blks).

Keep in mind that the depth of the index depends on the block size and the size of the index. Ideally, you know the value once the index rebuilt and set it as some kind of baseline later use with a small increase (no more than 2 depths) to determine whether to do a rebuild.

Leave a Reply