Re: How many people here rebuid index regularly?

On Oct 5, 4:56 pm, Hasta <hasta..._at_hotmail.com> wrote:
> In article <Y1hNi.7131$DF2.4804_at_trndny09>, "charlie cs" <charliecs
> (nospam)@hotmail.com> says...
>
>
>
> > We are on 9i Oracle.
>
> > Search on the internet, some expert say
> > "In the overwhelming majority of cases, indexes are extremely well
> > self-maintained and index rebuilds are NOT required, ever."
>
> > Tom Kytes in his website says
> > "The time lag between index rebuilds should be approximately FOREVER.
> > Tell me -- why why why WHY are you rebuilding them on a schedule? What is
> > the scientific
> > basis in reality that is driving you to do this???? "
>
> > And some other site says:
> > "First rule of thumb is if the index has height greater than four, rebuild
> > the index. For most indexes, the height of the index will be quite low,
> > i.e. one or two. I have seen an index on a 3 million-row table that had
> > height three. An index with height greater than four may need to be rebuilt
> > as this might indicate a skewed tree structure. "
>
> > Which one is right?
>
> > Thanks for your comments
>
> > Thanks
>
> 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!

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;" Received on Fri Oct 05 2007 - 05:51:30 CDT