Re: problem with a query

If you check carefully I think you will notice that there is a significant difference between the quotation and the original post.

>> >"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

vs

> released by the old value may never be used again.

Judging from your description of your application you are deleting (or changing the indexed value for) a large percentage but not all of the rows at the low end of the index, and then doing index range scans across that end of the index.

Because Oracle does not coalesce nearly empty index blocks (until 8.1) the range scan could be finding 1 row per block at the low end of the index and 100 rows per block at the high end of the index.

However, if you empty an index block completely it will go on the free list and be re-used.

The problem is not that 'Oracle does not re-use' the space from deleted rows, the problem is in effect 'if you don't give Oracle an entry that is supposed to use that space, it can't be used'

Oracle 8.1 introduces 'alter index xxx coalesce' for this reason - Oracle 8.0 introduced 'alter index rebuild online'. There are pro's and con's to the two methods.

Reverse key indexes are supposed to address the issue of buffer busy waits on the right-hand index leaf of sequence based indexes, and the associated pinging problem in OPS.

Have you implemented Reverse key indexes by the way ? I think you may find that in the general case this double the size of your index anyway. In your particular case the volume of deletes may mean that you don't hit quite this limit, but it is highly likely that range scans on the index will start fairly high and stay fairly high rather than starting cheap and going exponentially expensive since adjacent values will be scattered randomly through the index leaves.

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

John Gasch wrote in message <38EBFB3F.67100EDA_at_erols.com>...


>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

> 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.

>