Re: long running select min(timestamp) query

On Feb 2, 3:01 am, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> <bobdu..._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-rebui...
> >>> 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.
>

And now I see Richard has published some more details about coalesce in his Feb 5 and 6 blogs: http://richardfoote.wordpress.com/

jg

--
@home.com is bogus.
"Bravery is being the only one who knows you're afraid." - David
Hackworth