Re: How many people here rebuid index regularly?

On Oct 5, 1:16 pm, hjr.pyth..._at_gmail.com wrote:
> On Oct 5, 12:10 pm, "charlie cs" <charliecs(nospam)@hotmail.com>
> wrote:
>
>
>
> > 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
>
> You have to be certifiably insane to do index rebuilds just because
> some rule-of-thumb somewhere says that if some metric you don't
> understand reaches some value whose significance you don't understand
> says you should.
>
> What Tom Kyte is saying is "APPROXIMATELY forever". In other words,
> not absolutely never, but very, very rarely when the specific
> circumstances require it. A skewed index would be such a circumstance
> (as would moving a table, doing DDL on a partitioned table with global
> indexes, realising your queries are for Column A, B and your index is
> built on Column B, A (though that's a re-create not a rebuild, but
> close enough) and so on).
>
> If you simply say, "Height 4... rebuild", that is a silly way to
> manage a database. It's akin to saying, "Buffer Cache Hit Ratio = 75%,
> must increase db_cache_size!" Likewise "deleted leaf rows > 20% of
> leaf rows" is a daft way to go, because deleted leaf row space will
> sort itself out over time... unless the index is on something like a
> sequence (or time), and thus monotonically incrementing, because then
> you will never re-use that deleted leaf row space.... unless the
> deleted leaf row space comes in such large chunks that it causes
> entire blocks to be vacated, in which case you will...
>
> I mean, in short, the subject is complicated and boiling it down to
> moronic rules of thumb is a mug's games.
>
> But, generally and with due caution, it is more or less fair to say
> that if you HAD to have a rule of thumb that applied in a rhetorical
> 95% of cases for a rhetorical 95% of the time, it would be, "don't
> rebuild your indexes". The risks of adopting that approach far
> outweigh the costs associated with the other one of "rebuild your
> indexes routinely because otherwise your CPU cycles just go to waste".
>
> Certainly, you do not want to be ROUTINELY rebuilding your indexes.
> (Having just scheduled a routine rebuild of an Intermedia index, I
> know there are exceptions even to that rule provided you get precise
> enough about it! But I'm assuming we're talking about regular b-tree
> indexes here otherwise).

I meant, of course, "...the BENEFITS of adopting that approach far outweigh the costs associated with the other one...."

Apologies for mangling my grammar.

:0 Received on Fri Oct 05 2007 - 01:19:23 CDT