SQL Processing - Relational Database Index Design and the Optimizers - page 34

In DB2 for z/OS the predicates that are too complex for the optimizer in
the sense that index screening is not enabled are called stage 2 predicates. The
opposite term is stage 1. Many product guides do not discuss index screening
at all. Hopefully, this means that the products do index screening whenever it
is logically possible; before making this assumption, however, it could well be
worthwhile performing a simple experiment such as the following:
A table having four columns is created with 100,000 rows; the first three
columns, A, B, and C, each have 100 different values. Column D is added to
ensure that the table is accessed; it should be long enough to allow only one table
row per page. An index (A, B, C) is created and SELECT D FROM TABLE
WHERE A = :A AND C = :C is run to determine the number of table rows
accessed. We are assuming that all the relevant rows are FETCHed and that
the index is in fact used; with only one row per page and consequently having
100,000 pages in the table, it is unlikely that it wouldn't be. If the observed
number of table pages accessed is close to 10 (0 . 01 × 0 . 01 × 100 , 000; the first
0.01 for matching, the second for screening), index screening must have been
used for predicate C = :C. If it is closer to 1000 (0 . 01 × 100 , 000; only the first
0.01, used for matching), then it hasn't. This experiment can then be repeated at
any time with complex predicates to determine whether they are too difficult for
the optimizer.
Chapter 6 discusses in some detail difficult and very difficult predicates,
together with the implications regarding matching and screening.
Monitoring the Optimizer
When a slow SQL call is identified, the first suspect is often the optimizer; perhaps
it chose the wrong access path. A facility is available in relational DBMSs to
explain the decision made by the optimizer; this is called EXPLAIN, SHOW
PLAN, or EXPLAIN PLAN and is discussed in Chapter 7. All references to
EXPLAIN from now on should be taken to apply to all three terms.
Helping the Optimizer (Statistics)
The optimizer may have made a wrong choice because the statistics it uses as
a basis for making cost estimates were inadequate; perhaps the options chosen
when the statistics were gathered were too limited or the statistics are out-of-date.
These statistics are usually gathered on request, for example, by running a special
program called RUNSTATS in DB2 for z/OS.
The default statistics gathered normally include basic information, such as
the number of table rows and pages per table, the number of leaf pages, the cluster
ratio per index, and the number of distinct values for some columns or column
combinations (termed cardinality) as well as the highest and lowest values (or the
second highest and the second lowest) for some columns. Other optional statistics
provide more information about the value distribution of columns and column