Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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 <hasta_l3_at_hotmail.com>
Date: Fri, 5 Oct 2007 18:26:25 +0200
Message-ID: <MPG.2170841994c1cc35989691@news.dommel.be>


In article <1191581490.312563.10430_at_o80g2000hse.googlegroups.com>, hjr.pythian_at_gmail.com 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...
> >
> > http://groups.google.be/group/comp.databases.oracle.server/browse_thr...
> > 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

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US