>> what a load of rubbish
No, the junk that indexes never need rebuilding is anotther of those myths, don't buy it!
>> you need to read Richard Foote's index presentation
I think Foote has recognized that there are indeed cases where index rebuilding has proven improvements.
Index rebuild/coalesce not only reduces disk space consumption, it improves SQL performance:
>> In which cases DBA goes for index rebuilding ?
Usually, indexes that become physically sparese (not full blocks) as a result of deletes:http://www.dba-oracle.com/t_index_rebuilding_issues.htm
See Oracle MetaLink note 122008.1 for the officially authorized script to detect indexes that benefit from rebuilding. This script detects indexes for rebuilding using these rules: Rebuild the index when these conditions are true:
- deleted entries represent 20% or more of the current entries.
- the index depth is more then 4 levels.
Oracle's index rebuilding guidelines appear in Metalink note 77574.1 (dated April 2007) recommends that indexes be periodically examined to see if they are candidates for an index rebuild:
“When an index is skewed, parts of an index are accessed more frequently than others. As a result, disk contention may occur, creating a bottleneck in performance.
It is important to periodically examine your indexes to determine if they have become skewed and might need to be rebuilt.”
The note also says that the index rebuilding criteria has changed since the advent of Oracle9i, where a blevel > 4 was a good threshold. It also notes that the pct_deleted column in index_stats from “alter index xxx validate structure” provides a guideline for index rebuilding candidates:
“Prior to 9i, if the BLEVEL is more than 4, it is recommended to rebuild the index. . .
The PCT_DELETED column shows what percent of leaf entries (index entries) have been deleted and remain unfilled.
The more deleted entries exist on an index, the more unbalanced the index becomes. If the PCT_DELETED is 20% or higher, the index is candidate for rebuilding.”
Metalink Note:46757.1 titled "Notes on Choosing an Optimal DB BLOCK SIZE" says that there are some tangible benefits to using a larger blocksize:
- Using bigger blocks means more data transfer per I/O call; this is an advantage since the cost of I/O setup dominates the cost of an I/O. . .
- Using bigger blocks means more space for key storage in the branch nodes of B*-tree indexes, which reduces index height, which improves the performance of indexed queries.
- Using a block size that is k times bigger than your current one will save you (k-1)f/(kb-f) bytes of space for large segments, where f is the size of a block's fixed block header (61 bytes for tables, 57+4n for n-table clusters, 113 for indexes). For example, you will conserve about 4% of data storage (4GB on every 100GB) for every large index in your database by moving from a 2KB database block size to an 8KB database block size.
- When using large block there are less probability of chained and migrated rows, which in turn reduced the number of reads required to get the information.
Other evidence on index rebuilding
Ken Adkins, a respected Oracle author, notes that it is often difficult to pinpoint the exact reason that indexes benefit from a rebuild:
“The DBAs were pulling out their hair until they noticed that the size of the indexes were too large for the amount of data in the tables, and remembered this old “myth”, and decided to try rebuilding the indexes on these tables.
The DELETE with the multiple NOT EXISTS went from running for 2 hours to delete 30,000 records, to deleting over 100,000 records in minutes. Simply by rebuilding the indexes….”