Controlling Optimizer with a User-Specified Restriction Factor

The restriction factor is the fraction of a table between 0 and 1 that is returned as a result of the application of a specific WHERE condition. The lower the value, the greater the likelihood that the access method associated with that condition will be chosen by the optimizer. This factor is computed by the optimizer based on the type of relational expression and the data distribution values for the column. Note that you can override the optimizer's estimate by using a non-standard RDM SQL feature. A relational expression, relexpr, can be written as "(relexpr, factor)", where factor is a decimal fraction between 0 and 1 indicating the percentage of the file restricted by relexpr.

For example, in the following query from the NSF database, where the optimizer would normally access the data using the awardno key, the specified restriction factors will actually cause the optimizer to use the award_date key.

select * from award 
 	where (awardno = 70246, 1.0) 
 	and   (award_date > date "2002-07-01", 0.00001);

When statistics used by the optimizer are not accurate enough for a given query and the result is unsatisfactory, you can use this feature to override the stats-based restriction factor and substitute your own value. However, your use of this feature renders the query independent of future changes to the data distribution statistics.