Re: long running select min(timestamp) query

From: joel garry <>
Date: Wed, 6 Feb 2008 15:09:52 -0800 (PST)
Message-ID: <>

On Feb 2, 3:01 am, "Jonathan Lewis" <> wrote:
> <> wrote in message
> > Anyways, Charles has helped me out bigtime by pointing me in the right
> > direction:
> >>>
> >>> 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:


-- is bogus.
"Bravery is being the only one who knows you're afraid." - David
Received on Wed Feb 06 2008 - 17:09:52 CST

Original text of this message