Archive for February 2013

Index Clustering Factor finally more realistic

I just stumbled upon this bug reference on My Oracle Support:

Bug 13262857  Enh: provide some control over DBMS_STATS index clustering factor computation

This enhancement was long due. Previously, when computing the clustering factor during gathering statistics, the value was incremented, whenever the row was not found in the same block as the previous row. Now, it is finally possible to determine how many blocks should be considered when computing clustering factor. The patch delivers an improved DBMS_STATS package body that can be used to set preferences with value TABLE_CACHED_BLOCKS.

The flaw in the over-simplistic and pessimistic original computation was something that Jonathan Lewis documented in his Book “Cost Based Fundamentals” in year 2006 along with an workaround to manually gather improved clustering factor values!

The relevant chapter of this book is available as PDF at this URL.