SQL/MP Query Guide

Improving Query Performance Through Query
Design
HP NonStop SQL/MP Query Guide—524488-003
3-11
Transformation of Predicates
Expanding Equality Predicates
You can code the same query in several different ways. Each way generates the same
result, but if Query Rewrite did not exist, the queries could differ widely in performance,
as in these examples:
SELECT * FROM T, U WHERE T.C = :hv AND U.D = :hv ;
SELECT * FROM T, U WHERE T.C = U.D AND T.C = :hv ;
SELECT * FROM T, U WHERE T.C = U.D AND U.D = :hv ;
SELECT * FROM T, U WHERE T.C = U.D AND T.C = :hv
AND U.D = :hv ;
All these alternatives produce the same result. For each alternative, the optimizer
would consider a different subset of the possible access plans, resulting in a variance
in performance.
For instance, no hash joins would be considered for the first query because T.C = U.D
is not explicitly stated. The nested loop join method is the only method that can be
used when no equijoin predicate directly connects a pair of join columns.
The second query would require a 100 percent scan of table U because U.D = :hv is
not explicitly stated. The third alternative would perform a 100 percent scan of table T
because T.C = :hv is not explicitly stated.
The fourth alternative states all equality relationships explicitly. Therefore, all
appropriate join methods would be considered. The optimizer would apply the T.C =
:hv and U.D = :hv predicates before the join.
Using Query Rewrite, the optimizer adds any missing predicates so that it has more
plans to consider and a better chance of executing the best plan. In the example, the
optimizer converts the first three queries into the fourth.
Before adding missing predicates, the optimizer considers whether additional equality
predicates are potentially useful for optimization and then takes the appropriate action.
When Additional Equality Predicates Are Useful
The optimizer might consider additional equality predicates useful if their positions
within the query allow them to execute independently from other predicates. It expands
predicates that are not within the scope of a logical OR operator and queries that are
eligible for OR optimization. For details about OR optimization, see Using OR
Operators in Predicates on page 3-22. For information on how you can affect the
expansion of predicates, see Controlling the Expansion of Predicates on page 3-20.
Eliminating Unnecessary Predicates
The optimizer eliminates predicates not necessary for processing, such as redundant
predicates.