Re: Index management

Snip>"To distill my advice on index rebuilds, I say to only rebuild indexes
which show, through proper analysis, to have problems. Examples of possible probelms are: a large clustering factor to dirty base table block ratio, [and he does go on to list a number of other tests which are not at issue in this thread]">

As usual, you only quote the part of the posting that supports your argument and ignore other relevant data. Using this method I can prove that because we in the USA drive on the opposite side of the road than those in England, we get tornados and they don't. As I have tried to tell you in words of one syllable or less, the clustering factor to
"used base table block" ratio is only one thing that should be used to
see if an index needs to be rebuilt. The number of levels and the amount of "white" space in the index are also determining factors. As someone so appropriately stated, rebuild the index when the benefits of doing so exceed the cost, unfortunately you can't determine this until after they are rebuilt. I am just trying to provide guidelines as to when the index becomes a candidate for rebuild. Notice, I said
"guideline" not absolute rule, not law of nature, just guideline.

You want to force me into this idiot box you have created, sorry, I don't fit, I hope you like it in there. If you refuse to see the entire picture, then no one can correct your misunderstandings.

The idea that you should optimize an index for inserts and updates is totally odd to me as indexes are used for optimization of select processing. If indexes are causing that big of a problem during inserts and updates then maybe you need to review what you are indexing and why. Avoiding index rebuilds by crippling the performance of indexes by making them too-broad (i.e. sparse) is also an odd idea.

The next time I have a client who needs to consider index rebuilds to improve performance I will capture some stats for the list and will post before and after data. Those that want to see what report I use to gather the index stats data can email me, I supply it to any who ask. I have no magic formula for clustering factor, Oracle calculates that readily enough and provides it in the dba_indexes view. The number of dirty, used, occupied or whatever euphemism you wish to use, blocks is also readily available in dba_tables. A ratio is determined by the division of two values, so when I say the ratio of clustering factor (a single, easy to obtain number) to dirty blocks (another easy to obtain number) then I have stated my formula. Since the actual value of this ratio for "badness" varies from table to table and index to index, I usually just say, when it exceeds several times the number of dirty blocks and when really pressed I say start at 8 to 10 times the value and do further analysis (many people want a hard number, sorry, there isn't one.)

I don't say, rebuild all indexes, which would be as bad as never rebuild indexes. Both of these can lead to problems. Analyze (as in look at various statistics), rebuild when indicated. Think for yourself.

Mike Ault Received on Mon May 10 2004 - 09:44:30 CDT