Mosc auto updating
For example, indexes that are always accessed vis an index unique scan" will never need rebuilding, because the "dead space" does not interfere with the index access.
Only indexes that have a high number of deleted leaf blocks and are accessed in these ways will benefit from rebuilding: Getting statistically valid: proof from a volatile production system would be a phenomenal challenge.
However, excessive activity within a table can cause Oracle indexes to dynamically reconfigure themselves.
This reconfiguration involves three activities: Indexes require rebuilding when deleted leaf nodes appear or when the index has spawned into too many levels of depth.
# deleted blk gets Index keys keys leaf rows Height per access-------------------- ------ ----- -------- ------ -----CUST_IDX 1 423,209 58,282 4 6EDU_IDX_12 433 36,272 7,231 3 2CUST_IDX 12 1,262,393 726,361 4 6end loop;end;/ select 'exec DBMS_STATS.
Some experts suspect a Placebo Effect may be at work here, and the end-users, knowing that they have new index trees, report a performance gain when none exists.
This Kim Floss article shows the Oracle 10g segment advisor recommending a rebuild of an index: 'The page lists all the segments (table, index, and so on) that constitute the object under review.
The default view ("View Segments Recommended to Shrink") lists any segments that have free space you can reclaim.' There are many myths and legends surrounding the use of Oracle indexes, especially the ongoing passionate debate about rebuilding of indexes for improving performance.
For now, the most any Oracle professional can do is to explore their indexes and learn how the software manages b-tree structures.
When can we "prove" a benefit from an index rebuild?