Get Oracle to favor particular indexes

oracleperformance

Oracle 11.2 R2 RAC

I have a database which has many dynamic and ad hoc queries run again it. The stats are all up to date, but often because of large numbers of complex joins and heavy parameterization, the CBO picks a less than optimal plan.

There are a couple of tables that are extremely common and if they exist in the query, its almost always a win to force a particular index rather than a full table scan (which is what it tries to pick most often).

Is there a way to artificially get the CBO to favor particular indexes without having to hint individual queries? Not a hint on the queries, but a way to make the index itself look cheaper or such?

Best Answer

The cost of an index depends on its clustering factor.

Clustering factor measures how well aligned are the rows in the table - in respect of the columns of the index in question. It is calculated as: inspect all the rows from table in order (order is defined by the index columns). Every time a row is found to be in a different (not the currently inspected) block, increase the clustering factor by 1. If consecutive values in a table are near each other in the same block, the clustering factor of the index will be low. If consecutive values are scattered across the table, the clustering factor will be high.

Simply put, the lower the clustering factor is, the cheaper the index becomes.

Despite the myth, the clustering factor can not be decreased by rebuilding the index, because it depends on the table data distribution. However, you can set the clustering factor manually by:

begin
  dbms_stats.set_index_stats
  (
   ownname       => 'USER1', 
   indname       => 'INDEX1',
   clstfct       => 1234,
   no_invalidate => false
  );
end;
/

Where 1234 is the desired clustering factor. The lowest valid value of the clustering factor is the number of the table blocks. The highest valid value of the clustering factor is the number of the table rows. But this does not mean you can not set it lower or higher.

This setting will be lost on the next index statistics collection.

You can use the above to change other statistics as well that have effect on the cost: blevel (indlevel) or leaf_blocks (numlblks).