DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: stagnated index: space can never be used again

  1. #1
    Join Date
    Nov 2000
    Posts
    212
    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."

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Nov 2000
    Posts
    212
    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?

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width