SQL/MP Query Guide

Improving Query Performance Through Query
Design
HP NonStop SQL/MP Query Guide—524488-003
3-9
Transformation of Predicates
•
Queries written by users who are not NonStop SQL/MP experts
Optimization time can increase when these transformations take place. Although the
transformations are automatic, you can use a DEFINE to control compilation time and
still receive optimization benefits. For information on the DEFINE, see Controlling the
Expansion of Predicates on page 3-20.
How Query Rewrite Works
When the optimizer determines that a query can benefit from Query Rewrite, it
repeatedly performs these tasks until the query reaches a final state:
•
Transforms unnecessary left joins to inner joins
•
Propagates constants
•
Expands equality predicates
•
Eliminates unnecessary predicates
•
Simplifies predicates
The EXPLAIN plan for the query shows the results of these tasks.
Transforming Left Joins to Inner Joins
In a left join, if a row from the table specified on the right side of the left join operator
does not satisfy a search condition, SQL preserves the row from the table specified on
the left by extending it with as many null values as there are columns in the table on
the right. These rows are called null-augmented rows.
If a WHERE or INNER JOIN predicate is certain to eliminate all of the null-augmented
rows generated by a left join, then the optimizer transforms the left join into a more
efficient inner join.
Consider this view and query:
CREATE VIEW V AS SELECT T.C, U.D
FROM T LEFT JOIN U ON T.C = U.C ;
SELECT * FROM V WHERE D = 1 ;
T is the table on the left side of the JOIN keyword. T contains no column D. To
preserve rows from the T table, SQL extends each row that does not satisfy the
condition D = 1. The result is the generation of null-augmented rows.
If an inner join were performed, only the rows that satisfy the condition D = 1 would be
returned, and the null-augmented rows would not be generated. Therefore, for the
query in the example, the optimizer converts the left join to an inner join.
The same is true in this query:
SELECT * FROM V WHERE D = 1 or D = 2 ;