Re: long running select min(timestamp) query
Date: Wed, 6 Feb 2008 15:09:52 -0800 (PST)
Message-ID: <60ad6b7b-f7b0-4943-8de2-137bfb92e21b@m34g2000hsf.googlegroups.com>
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 HackworthReceived on Wed Feb 06 2008 - 17:09:52 CST