Re: Index management

Well,

After extensive testing (a couple of hours worth) using various combinations of insert, update and delete I was not able to achieve differing values for clustering factor with index rebuilds unless the base table is also rebuilt (which would be expected due to the redistribution of rows as pctfree is applied to rows that have been updated.) However, I was not able to fully simulate the effect of possibly hundreds of simultaneous IUD activities, however, that is a moot point.

Note that rebuild of the base table after thousands of IUD actions resulted in a net increase in clustering factor. To test this create a simple CTAS of the dba_objects view, create a couple of indexes on it, analyze the table using compute and check the CF of the indexes, then create a PL/SQL or other script that does insert, update and delete of various combinations of indexed values (I like to use a modulus of various constants against the object_id so I get a spread across the table with my statements). Once you complete your changes, do another table analysis, then recheck the clustering factors. Then rebuild the indexes and recheck (afer re-analysis). If you have done a number of updates, then rebuild the base table (alter table x move;), you will also have to rebuild any indexes on the table, then analyze the table again and check CF.

The problem lays with the fact that this was not a complex environment involving millions of rows and many users, but for the time being I will concede the point that a simple index rebuild, in a simple environment, will not result in a change to clustering factor for a standard b-tree type index. I will change my lectures and update the tuning materials I have published to reflect this finding (I may even give a word of mention to the fellows Foote and Rogers...) when I am wrong, I admit it.

However, the CF to used baased table block ratio still tells you what indexes are becoming totally randomized and can still be used as a diagnostic pointer should the cf to used blocks ratio change substantially (this of course means tracking it for major tables.) Also, since it is a direct factor in index cost analysis for the CBO steps such as re-ordering concatenated index columns or rebuild of the base table in index order to reduce clustering factor are still valid.

I still advocate rebuild of indexes that are sparse or that demonstrate improper levels for their complexity.

The decreases in clustering factor I noticed at sites may have been due to stale statistics, use of estimate rather than compute or other factors.

However, I will endeavor to check this in a more complex IUD situation than I can simulate on an XP laptop and if anomalous behavior (i.e. reduction in CF) does occur, I will report back to the list with the scenario and the statistics.

Mike Ault Received on Mon May 10 2004 - 18:44:33 CDT