Re: long running select min(timestamp) query

<bobdurie_at_gmail.com> wrote in message
news:805c5d03-8e41-4fa9-b81f-6fd153f64060_at_v67g2000hse.googlegroups.com...
> Anyways, Charles has helped me out bigtime by pointing me in the right
> direction:
>>> http://richardfoote.files.wordpress.com/2007/12/index-internals-rebuilding-the-truth.pdf
>>> Pages 133-135 show what is happening in your situation. It appears
>>> that this is one of the few cases where an index may need to be
>>> rebuilt...
>
> I rebuilt the index, performed the search, got a result instantly,
> only 3 consistent gets!!!
>
> One followup question, that you'll all likely laugh at. I got into
> this "mess" by having a table that we frequently perform delete from
> TABLE where DATE < ? - this situation is going to continue to arise.
> Should i simply schedule frequent index rebuilds? I understand
> partitioning the data is probably the way to go, but what is frequent
> rebuilds the simplest solution here (by simple, i mean least knowledge/
> testing/sql involved)?
>
> Thanks again to all your help!!! Sincerely,
> Bob

Sorry about getting into this so late - but your problem is a classic 'delete the left hand end' issue, and one of the reasons why you want to use the COALESCE command after a big delete.

It's probably somewhere in Richard's presentation, but when an index leaf block is emptied, it is linked to the free list, but also stays in place in the index structure.

Your min() query was doing a '(min/max)' full scan, which means it was going directly to the left (low) end of the index in expectation of finding the value there. However, since you've done a thorough delete of a lot of low-value rows, the run-time engine got to the bottom block, and had to walk a long walk through a lot of leaf blocks before finding the first leaf block with any data in it.

A call to coalesce will collapse together adjacent leaf blocks to reduce leaf block counts, and detach empty leaf blocks from the structure so that subsequent queries don't have to walk through them.

The cost / benefit balance is:

    each coalesce requires a full walk of the index - so don't do     it when you have a large index with only a small amount of     recoverable space.

    failing to coalesce (for your query) requires a lot of empty     leaf blocks to be walked - how many times do you want     to let this happen, and how slow can the query be, before     you coalesce.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html