Index screening

In index screening, predicates are specified on index key columns but are not part of the matching columns.

Those predicates improve the index access by reducing the number of rows that qualify while searching the index. For example, with an index on T(C1,C2,C3,C4) in the following SQL statement, C3>0 and C4=2 are index screening predicates.

SELECT * FROM T
  WHERE C1 = 1
    AND C3 > 0 AND C4 = 2
    AND C5 = 8;

The predicates can be applied on the index, but they are not matching predicates. However, C5=8 is not an index screening predicate, and Db2 must evaluated that predicate when data is retrieved. The value of the MATCHCOLS column of PLAN_TABLE is 1.

The STAGE column of DSN_FILTER_TABLE identifies predicates that Db2 uses for index screening.