Predicate filter factors

By understanding of how DB2® uses filter factors you can write more efficient predicates.

Begin program-specific programming interface information.
The filter factor of a predicate is a number 0 - 1. The number estimates the proportion of rows in a table for which the predicate is true. Those rows are said to qualify by that predicate.

For example, suppose that DB2 can determine that column C1 of table T contains only five distinct values: A, D, Q, W, and X. In the absence of other information, DB2 estimates that one-fifth of the rows contain the value D in column C1. Then the predicate C1='D' has the filter factor 0.2 for table T.

How DB2 uses filter factors:

DB2 uses filter factors to estimate the number of rows that are qualified by a set of predicates.

For simple predicates, the filter factor is a function of three variables:

  • The constant value in the predicate; for instance, 'D' in the previous example.
  • The operator in the predicate; for instance, '=' in the previous example and '<>' in the negation of the predicate.
  • Statistics on the column in the predicate. In the previous example, an example statistics is the information that column T.C1 contains only five values.
Tip: You can control the first two of those variables when you write a predicate. Your understanding of how DB2 uses filter factors can help you to write more efficient predicates.
Values of the third variable, statistics on the column, are kept in the DB2 catalog. You can update many of those values by running the RUNSTATS utility. You can also modify the catalog table values.
Important: Before you modify the catalog with statistics of your own choice, you must understand how DB2 uses filter factors and interpolation formulas.

End program-specific programming interface information.