Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: How many people here rebuid index regularly?

Re: How many people here rebuid index regularly?

From: Hasta <>
Date: Fri, 5 Oct 2007 18:26:25 +0200
Message-ID: <>

In article <>, says...
> > We do rebuild indexes, because it was measured to improve
> > performance on our system (9i).
> >
> > See the thread "Proving or Debunking the need for rebuilding"
> > in this group for a report...
> >
> >
> > hl=fr&lnk=gst&q=+Proving+or+Debunking+the+need+for+rebuilding&rnum=1#
> > 9e32aa3cf9282f81
> >
> > --- Raoul
> Well, from what I read of that thread, you didn't measure very much
> before, you weren't entirely certain on what to measure afterwards,
> and it was all a bit vague and airy!

Well, I cannot really agree, Howard

As I recall, that was a very long, very controlled measurement effort, whose goal was to check whether our system did benefit from index rebuilds.

I really expected to see that index rebuilds were not needed.

To my surprise, the test showed that the rebuilds were beneficial. Trouble is that while the tests show with certainty a very measurable change, I dont have data to know with certainty why.

The best hypothesis right now is :
* Many of our queries are optimized with index range scans. * Some important indexes do index monotonically increasing fields - mainly sequence and date primary keys.
* The delete pattern is such that 9 out of ten rows with consecutive dates or sequences are deleted, leaving the corresponding indexes with non empty but sparsely populated leaf blocks. - Therefore range scans take longer

As I recall, and this is only an hypothesis... All the information I have is in the referenced thread - albeit in bits and pieces, sorry.

About repeatability... For various reasons it has happened that some of our customers have been running without rebuilds for a few weeks. And complained to our technical support that the system was becoming slow. The effect I have measured is therefore very real, experienced by actual users in the field.

> But that's standard fare in the 'I know my index rebuilds are
> beneficial' industry: no-one actually every properly measures a darn'd
> thing!
> I don;t mean that in a critical way, by the way. More a frustration
> thing: everyone has anecdotes, few have facts and figures.
> Anyway, even if you happen to be of that rare breed, someone for whom
> an index rebuild genuinely produces tangible, repeatable and
> measureable benefits, fine: I have no problem with that. So long as
> you, some third party with a bouffant hair-do or some poor innocent
> newbie passing over this thread months or years from now understands:
> that is not usual and it doesn't provide the basis for a simplistic
> rule of thumb of the sort that says, "If Height>= 4 then rebuild;"

Sure. I am not claiming anything more than what I have observed *on our system* - and not suggesting anything more than "measure your system" Received on Fri Oct 05 2007 - 11:26:25 CDT

Original text of this message