SQL/MP Query Guide

Improving Query Performance Through Query
Design
HP NonStop SQL/MP Query Guide—524488-003
3-10
Transformation of Predicates
In this query, the left join cannot be converted to an inner join:
SELECT * FROM V WHERE C = 1 OR D = 2 ;
The search condition C = 1 OR D = 2 selects rows in which C = 1, regardless of the
value of D.
Suppose that the left-join operation generates a null-augmented row in which C = 1
and D is null. A logical OR is true if either of its operands is true. For this row, the
condition C = 1 is true, so the row is included in the query result, regardless of the
value of D. The optimizer cannot convert this left join to an inner join because the
conversion would eliminate the null-augmented row and change the result of the query.
Propagating Constants
Propagation of constants deals with constant expressions and equivalence classes. A
constant expression is an expression that contains no subqueries and no column
references other than outer references, such as in this example:
:hva + :hvb * 2
An equivalence class is a set of expressions equal to each other. For example, in this
statement, 1 is a constant expression, and A, B, C, and 1 are all expressions in the
same equivalence class:
SELECT * FROM T WHERE
A = 1
AND B = 1
AND C = A ;
Some queries execute faster if members of the same equivalence class are replaced
by constant expressions. This replacement is called constant propagation. If a member
of an equivalence class is a constant expression, then most occurrences of the other
members of the class can be replaced by the constant expression; for example:
A = B + C AND C = 1
can be transformed into:
A = B + 1 AND C = 1
Because the LIKE predicate can distinguish character strings with different numbers of
trailing spaces, LIKE predicates are exempted from constant propagation. For
example, if (C = D) and (C LIKE X), the optimizer cannot infer that D LIKE X.