Re: How many people here rebuid index regularly?

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). Received on Thu Oct 04 2007 - 22:16:18 CDT