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."
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).
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?