Factors Affecting the Index Design Process - Relational Database Index Design and the Optimizers - page 92

Summary

Only BT predicates can participate in defining an index slice, unless the access
path is a multiple index scan.
Let us consider a WHERE clause that defines large men (SQL 6.5):

SQL 6.5

WHERE SEX = 'M'

AND
(WEIGHT > 90
OR
HEIGHT > 190)

'M' is BT. If the WEIGHT predicate
is false, the HEIGHT predicate may be true and vice versa. Neither can, by itself,
cause the row to be rejected.
This is why we made the following statement: Include only predicates that
are simple enough for the optimizer in Chapter 4 when using the ideal index
algorithm for candidates A and B. For SQL 6.5, only column SEX could be used
in step 1 of the algorithm.

Only the first simple predicate SEX =

IN Predicate

The predicate COL IN (:hv1, :hv2 ... ) may cause problems for optimizers. In
DB2 for z/OS V7 for instance, only one IN predicate can be matching and so
participate in defining the index slice, but the column in the matching IN predicate
will not necessarily be the last matching column. Therefore, when the ideal index
Candidate A is derived, the column of the most selective IN predicate should be
included in step 1. SQL 6.6 provides an example.

SQL 6.6

DECLARE CURSOR66 CURSOR FOR
SELECT A, B, C, D, E

FROM TX

WHERE A = :A

AND
B IN (:B1, :B2, :B3) FF = 0...10%

AND
C>1
AND
E IN (:E1, :E2, :E3) FF = 0...1%