Re: problem with a query

  I've discovered what the problem was. I ran a 'dbverify' and noticed problems with the datafile associated with the index in question.

  Thanks for the input.

In article <955006959.2873.0.nnrp-01.9e984b29_at_news.demon.co.uk>,   "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:
>
>
> 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.
> >
>
>

--
-Keith


Sent via Deja.com http://www.deja.com/
Before you buy.