SQL/MP Query Guide

Improving Query Performance Through Query
Design
HP NonStop SQL/MP Query Guide—524488-003
3-7
Transformation of Predicates
Transformation of BETWEEN Predicates
SQL transforms a BETWEEN predicate into the equivalent range predicate; for
example:
X BETWEEN Y AND Z
is transformed to
X >= Y AND X <= Z
Transformation of Predicates With the NOT Operator
A predicate with one or more NOT operators is transformed to simplify and reduce the
number of NOT operations; these series of transformations illustrates the process:
NOT ((a > b) OR (x < y))
becomes:
NOT (a > b) OR NOT (x < y)
and then becomes:
a <= b OR x >= y
When used with EXISTS, LIKE, or IS NULL, the NOT operator is not transformed; it
remains the same.
MDAM processes NOT predicates on key columns as key predicates and does key
accesses on them.
Transformation of IN Predicates
SQL always transforms an IN predicate into another form. The final form depends on
whether the expression of the IN predicate is a value list or a subquery.
IN Predicates With Value Lists
If the expression of an IN predicate contains a value list, SQL transforms the list into a
search condition with the predicates connected by one or more OR operators; for
example, this predicate:
DEPT_NUM IN (:hv1, :hv2, :hv3)
is transformed into:
DEPT_NUM = :hv1 OR DEPT_NUM = :hv2 OR DEPT_NUM = :hv3
If DEPT_NUM is the key prefix—the leading (leftmost) contiguous set of columns in the
key—then OR optimization might be performed on the query predicate. DEPT_NUM
need not be a key prefix for MDAM processing to take place.
SQL transforms the list of values into a search condition that has equality predicates
connected by one or more OR operators:
WHERE DEPT_NUM = :hv1
OR DEPT_NUM = :hv2
OR DEPT_NUM = :hv3