Bellow is excerpt from OEM help about stagnated index.
The question is: is it really such that index space can not be reused, i.e. index size is always equal to its greatest size ever reached and never less?
From OEM help:
:When an indexed value is updated in the table, the old
value is deleted from the index and the new value is
inserted into a separate part of the index. The space
released by the old value can never be used again. As
indexed values are updated or deleted, the amount of
unusable space within the index increases, a condition
called index stagnation. Because a stagnated index
contains a mixture of data and empty areas, scans of
the index will be less efficient. This test monitors
whether indexes specified by the Index name, Index
owner, Indexed object nmae, and Indexed object owner
parameters suffer from index stagnation. If an index
has stagnation, an alert is generated."
AFAIK free space from deleted index entries in index leaf blocks can be reused if and only if the block is totaly empty, meaning all previous entries from that block were deleted.
Does index stagnation really cause a problem? Doeas it make sense to bother about that at all?
Under what conditions "index stagnation" happens in a real world?
Yes, it does matter in real life. Because of this stagnation index becomes "skewed", much bigger than it would be necessary and thus much less efficient. That is why index rebuilding was introduced in Oracle RDBMS (in 8.0, I belive).