reduce the size of the index (especially the leaf
block count). This may result in three possible
The optimiser calculations may produce a smaller
value for the cost of using the index, so the
optimiser may use the index in more execution plans.
Typical queries that use the index may have to visit
fewer index blocks, and therefore run more
Because the index is smaller, it may become a better
survivor in the buffer cache LRU chain, so that
blocks from the index are found in the buffer more
frequently, and fewer blocks from other objects are
flushed to allow index blocks to be reloaded. If this
occurs, system-level I/O is reduced, resulting in a
possible performance gain for everyone.
select substr(segment_name,1,50), segment_type, extents, bytesIf the above query returns any row ????
where extents > 5
and segment_type = 'INDEX'
and owner = user
order by extents desc;