What problems are caused by index fragmentation?
Database growth The greater the index fragmentation, the more space is unused in the index B* tree. This means that the index segment and the entire database grow more quickly than is necessary. Poor performance by the index range scan If an index is fragmented, then index accesses using the index range scan may have to read more blocks than is actually necessary. The ‘buffer gets’ and ‘disk reads’ associated with this process increase the access time considerably. Poor global performance as a result of block displacements in the buffer pool If, due to index fragmentation, more blocks must be read into the Oracle buffer pool than is strictly necessary, blocks are unnecessarily displaced from other segments. This in turn means that the buffer must be repeatedly reloaded, which can impede the performance of the entire database. 4. What triggers an index fragmentation? Index fragmentation usually occurs when data is repeatedly deleted from the index and reinserted at a different location.
