Re: Index management

Ok.

I don't mind when folks disagree, however, when you attach names to quotes and then say in effect this person is an idiot and this is rubbish, it does appear to be a personal attack rather than a professional one.

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, a large number of levels (of course large is relative, some folks say 4 other 2 is where "large" starts), or a large amount of white space (I call it browned nodes, following with the leaf and tree metaphore.) I do not advocate rebuilding all indexes.

In the case of a truely random index (such as some text indexes and concatenated indexes) rebuild of the index will achieve nothing. Unless you order the table inaccordance with the index, the clustering factor will remain nearer to the number of rows than to the number of dirty blocks. However, reordering the index columns to better align it with the underlying table order can reduce clustering factor significantly.

In special indexes such as bitmaps, the clustering factor can increase dramatically with IUD activity, and a rebuild will reduce it just as dramatically.

Reduction in the size of the clustering factor improves the chance the index will be selected by the CBO since the clustering factor is a multiplier in the index cost.

When this advice has been followed we (the folks at TUSC and other consultants) have seen batch times reduced from 5 hours to 2 hours, significant reduction in index space usage and significant reduction in required IO against the index tablespace.

As to whether an index rebuild will affect clustering factor, yes it will if the index has been broadened due to node splits and other activities. Remember that as old data is deleted and new data is inserted into the base table the order of rows in the base table changes as blocks are moved on and off of the free/used extent areas. By definition the order of rows in a relational table is random. So a preloaded table that is perfectly ordered by key eventually falls into Oracle entropy if it undergoes delete and insert activities (the property which forces and ordered table into disorder.) This means that you may start with a perfect clustering factor but with no changes to the index, table level changes will alter the clustering factor and a rebuild will reduce it.

I agree that if the base table is held constant and only inserted into then there is little likelyhood that the indexes will need rebuilds. However, we all don't live in the town Perfect and we have to deal with the real world where both tables and indexes get messy and must periodically be cleaned up. Most of the arguments against index rebuilds seem to ignore the fact that both the base table and the index are in a state of flux in a high IUD environment and over time get misaligned, hence causing the clustering factor to not truly represent the ordering of the table against the the index. A rebuild realigns the index nodes to better reflect the structure of the underlying base table.

I completely understand the concept of clustering factor, both in an ideal world and in the real one. I must deal with the real one.

Mike Ault Received on Sat May 08 2004 - 09:29:03 CDT