Restriction Factors
A restriction factor is associated with each relational expression that is specified in the WHERE clause and is an estimate of the ratio of number of rows for which the expression is true to the total number of candidate rows. A candidate row is a row of the table being produced by the SELECT statement before the WHERE clause is evaluated. Restriction factors are used by the optimizer to decide between alternative access methods. Restriction factors are floating point values between 0 and 1 and are computed based on the kind of relational expression and the availability of the column data distribution statistics produced by the UPDATE STATS statement.
Table 2. Restriction Factor Computations
| column = value |
1/number of distinct values of column |
| columnIN (value[, value]…) |
number of values in list * (1/number of distinct values of column) |
| column >[=] value |
(max(column) - value) / (max(column) - min(column)) |
| column <[=] value |
(value - min(column)) / (max(column) - min(column)) |
| column BETWEEN loval AND hival |
(hival - loval) / (max(column) - min(column)) |
Table 3. Restriction Factor Computations With Column Distribution Statistics
| column = value |
Ratio of the histogram count for value to the number of sampled table rows, otherwise (i.e., no histogram entry for value exists) 1/number of distinct values of column. |
| columnIN (value[, value]…) |
SUM(restriction factor computed for each column = value in the list—see above). |
| column >[=] value |
Ratio of the sum of the histogram counts where the histogram values for column are >[=] value to the sum of all histogram counts for column. |
| column <[=] value |
Ratio of the sum of the histogram counts where the histogram values for column are <[=] value to the sum of all histogram counts for column. |
| column BETWEEN loval AND hival |
Ratio of the sum of the histogram counts where the histogram values for column are BETWEEN lovalAND hival to the sum of all histogram counts for column. |