Optimizers Are Not Perfect - Relational Database Index Design and the Optimizers - page 245

ž Over a long period of time, the optimizers have learned to handle more
predicates in an efficient way, often by transforming them into a more
optimizer-friendly form before access path selection; separate tools are also
available that suggest a rewrite of a given query for a given optimizer.
ž In enlightened organizations, every SQL programmer will have a pitfall
list, containing the most common difficult predicates for the current opti-
mizer version, and a suggestion for circumvention.
Each DBMS seems to have a different name for difficult predicates. In SQL
Server topics they are called nonsearch arguments ; some Oracle topics talk about
index suppression , DB2 for z/OS topics describe them as nonindexable predicates .
In addition to matching problems, an optimizer may also have screening
problems. With really difficult predicates, the DBMS is not even able to do index
screening . This means that the DBMS has to read a table row to evaluate a
really difficult predicate even though the predicate column or columns have been
copied to the index. In DB2 for z/OS, really difficult predicates are called stage
2predicates .
Probably one of the best known examples of these is the predicate :hv
BETWEEN COL1 AND COL2; often convenient, for instance, when the host
variable is CURRENT DATE and columns COL1 and COL2 are the start and
end dates for a period. In some environments, the programmer has to rewrite this
really difficult predicate as COL1 < = :hv AND COL2 > = :hv. Some optimizers
may already do the transformation automatically.
Non-BT
The Boolean operator OR in a WHERE clause often causes unpleasant surprises
because it may make a compound predicate too difficult for the optimizer. As
described in Chapter 6, non-BT predicates cannot be used to reject a row when
the predicate is evaluated false; they therefore impose a very serious restriction
in that they can participate in defining an index slice only with multiple index
access . A predicate is BT if a row can be rejected when the predicate is false.
Therefore, if a WHERE clause contains only ANDs (no ORs), all predicates will
be BT.
In many browsing transactions, as well as in batch job repositioning, it would
be convenient to use a cursor such as the one used in SQL 14.1 to express the
requirement to display the result rows that have not yet been displayed . At least
one program generator produces cursors like this for browsing transactions.
The user enters the three first characters of LNAME, for instance, JON. The
application program moves the following values to the host variables:
LNAMEPREV
In the first transaction JONAAA ... , in the subsequent transactions the last
LNAME value shown:
CNOPREV