> 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 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).

> 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_thread
> /thread/9579b57c3434d990/9e32aa3cf9282f81?
> hl=fr&lnk=gst&q=+Proving+or+Debunking+the+need+for+rebuilding&rnum=1#
> 9e32aa3cf9282f81

> --- Raoul

> "Hasta" <hasta...@hotmail.com> wrote in message
> news:MPG.216ffe82b29bfb0e98968f@news.dommel.be...

> > 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_thread
> > /thread/9579b57c3434d990/9e32aa3cf9282f81?
> > hl=fr&lnk=gst&q=+Proving+or+Debunking+the+need+for+rebuilding&rnum=1#
> > 9e32aa3cf9282f81

> > --- Raoul

> Raoul,

> Did you ever find time to work out which indexes were
> particularly relevant to the massive change in the batch
> job, and what the difference was between the slow and
> the fast runs ?

> Running statspack at its default level 5 once per hour across
> the batch job might give you some useful information about
> the SQL with the biggest time change, and what made the
> time change - level 6 would allow you to check if this was
> due to plan changes, and level 7 might show you where the
> I/O changed most.

> > 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

> > 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;"

> > > 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