The clustering_factor is very important for costing index range scans; but there are some features of Oracle, and some performance-related strategies, that result in an unsuitable value for the clustering_factor.
In many cases, we can predict the problems that are likely to happen, and use alternative methods for generating a more appropriate clustering_factor. We can always use the dbms_stats package to patch a correct clustering_factor into place.
If the clustering_factor is exaggerated because of multiple freelists, or the use of ASSM, then you can use Oracle’s internal code for generating the clustering_factor with a modified value for the second parameter of the sys_op_countchg() function to get a more realistic value.
If the clustering_factor is exaggerated because of reverse key indexes, added columns, or even column reordering, then you may be able to generate a value based on knowing that the real functionality of the index relies on a subset of the columns. If necessary, build the reduced index on the backup data set, generate the correct clustering_factor, and transfer it to the production index.
Adjusting the clustering_factor really isn’t hacking or cheating; it is simply ensuring that the optimizer has better information than it can derive (at present) for itself.
Unable to display preview. Download preview PDF.