Re: problem with a query

If I said something misleading, I apologize for that. However, your response bewilders me because you did not elaborate on your blanket critical statement.

At the risk of plagarism, I quote the following from "Advanced Oracle Tuning and Administration", Oracle Press, 1997, page 98:

	...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 may never be used again.
	As indexed values are updated or deleted, the amount of 
	unusable space withing the index increases - a condition 
	called index stagnation.

This malady is exacerbated when ascending values are inserted into an index column - i.e. a sequence number. Oracle8's Reversed key index feature was introduced specifically to remedy this.

I have found this to be the case in practice (my database applications have massively intense insert/update activity). My indexes frequently double or more in size over a period of days with an exponential decrease in performance. I was told by Oracle that this "index stagnation", also known as "index brown-out", necessitates rebuilding indexes.

Perhaps you might elaborate on your statement that "This is almost completely untrue". I'm open to learning something new.

John Gasch

Jonathan Lewis wrote:
>
> This is almost completely untrue.
>
> You need to specify much more carefully
> how you are using the index before you
> hit the circumstances where this is
> nearly true.
>
> --
>
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> John Gasch wrote in message <38EB98AB.35791841_at_erols.com>...
> >When rows are deleted from a table, the index slots are merely marked as
> >"deleted". If the index is on a monotonically increasing data value,
> >such as a sequence number, then the index will grow without bound over
> >time because the deleted slots won't get reused. The periodic rebuild
> >works because it reclaims the space from the deleted rows.
> >
  Received on Wed Apr 05 2000 - 00:00:00 CDT