Dynamic collection of index filtering estimates

When certain statistics are unavailable, Db2 might access non-leaf index pages to obtain accurate index filtering estimates and improve access path selection.

When an SQL statement meets the following criteria, Db2 might obtain statistics for access path selection from non-leaf index pages:

• The SQL statement contains local predicates that match for index access.
• The predicates contain literal values, or the one of the REOPT(AUTO), (ONCE), or (ALWAYS) bind options is specified.

Db2 records information about the collected statistics in the DSN_COLDIST_TABLE and DSN_KEYTGTDIST_TABLE tables.

The following conditions might trigger the collection of statistics during optimization:

• Statistics indicate that the matching index key range is outside the range of the LOW2KEY and HIGH2KEY values.
• Histogram statistics indicate that the matching index access-key qualifies no rows.
• Frequency statistics indicate that the matching index access-key qualifies no rows.
• Statistics indicate that one or more qualified partitions contain no rows.
• Statistics indicate that the table contains no rows.
• The table is defined with the VOLATILE option, or passes the threshold specified by the NPGTHRSH subsystem parameter.

The only way to prevent the collection of these statistics is to ensure that the preceding conditions the are not met. To do this you might need to run the RUNSTATS utility or collect inline statistics with another unitility to update missing or out-of-date statistics or alter the table so that it is no longer VOLATILE.

Db2 might not always access the index even when the preceding conditions are true. For example, in the case of a fully matched unique index, such that only one row qualifies for the matching predicate, Db2 does not obtain statistics from the index.