Re: Index management

<Important caveat>
Comments below only apply to B*Tree indexes in Oracle - IOTs,bitmap,function based and bitmap join indexes are specifically excluded. </caveat>

"Mike Ault" <mikerault_at_earthlink.net> wrote in message news:37fab3ab.0405100644.7b46db3e_at_posting.google.com...
> 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.

Thanks for the nice remark - I do try to stay on topic most of the time :).

There is a quite handy guideline for when to do index rebuilds (defined as issue ALTER INDEX ... REBUILD..). That would be 'don't do it'. I'd hope that guidelines covered the usual state of affairs and not unusual or edge scenarios.

You have offered the following if I understand you correctly.

1. Ratio of DBA_INDEXES.CLUSTERING_FACTOR/(DBA_TABLES.NUM_BLOCKS - DBA_TABLES.EMPTY_BLOCKS) This cannot ever be changed by ALTER INDEX ... REBUILD all 3 components refer to the table not the index. There is no way that this makes sense for a guidline I'm afraid. Your good indexes after 'fixing' will have the same ratio they did before you fixed them and thus will be candidates for fixing again.
2. The number of levels in an index. This *can* be changed by ALTER INDEX ... REBUILD but such a change will be very rare indeed most 4 level indexes for example are that height because they have to be because of the number of rows they are indexing. In addition it is likely to only save you a very few logical IOs (usually precisely 1 per sql statement). Thus I'd be very wary of this factor as well.
3. The amount of 'white space' in an index. This would seem to be the most likely factor to consider - I assume you mean large numbers of almost empty index blocks. For queries that do index access by primary or unique key this will also be irrelevant (it will make no difference at all to the IO if you rebuild the index). For some range scans this *might* lead you to rebuild the index, I guess then the question will be how many index leaf blocks fewer will you visit. This however is the change most likely to have a cost associated with it. AFTER you rebuild the index in a tightly packed manner you are highly likely to have increased leaf block splits due to DML.

> 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

I appreciate this offer - I'd like to see the before and after stats.

> I don't say, rebuild all indexes, which would be as bad as never
> rebuild indexes.

Never rebuild any b*tree indexes is actually much better advice than rebuild all indexes :(. It goes too far, but not much too far. The onus will always be on the person advocating a change to an object to make the case - preferably with numbers ahead of time to back it up.

> Both of these can lead to problems. Analyze (as in
> look at various statistics), rebuild when indicated. Think for
> yourself.

Cheers

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK
http://www.niall.litchfield.dial.pipex.com/