SQL/MP Query Guide

Improving Query Performance Through Query
Design
HP NonStop SQL/MP Query Guide—524488-003
3-41
Adding Join Predicates
100210
300350
600480
800660
--- 4 row(s) selected.
To eliminate the unnecessary join and include a join predicate, therefore eliminating the
Cartesian result, rewrite the query as follows:
SELECT ORDERNUM
FROM ODETAIL O
WHERE O.PARTNUM = 5100
AND QTY_ORDERED <
(SELECT AVG(QTY_AVAILABLE)
FROM PARTS P
WHERE P.PARTNUM = 5100) ;
For a cost analysis of both formulations of the query, see Section 5, Selectivity and
Cost Estimates.
Adding Join Predicates
Beyond the advantage of a single join predicate, additional predicates can increase the
choice of execution plans available to SQL without changing the meaning of the query.
Consider this query:
SELECT *
FROM T1,T2,T3
WHERE T1.A = T2.B
AND T2.B = T3.C
Adding this predicate can increase the combinations available to SQL:
AND T1.A = T3.C
Using Joins Instead of Subqueries
In general, look for ways to formulate your query with join operations instead of
subqueries. The use of join operations can reduce I/O operations, reduce message
traffic, and increase the flexibility with which SQL can choose an execution plan.
When you use a subquery, you direct SQL to perform the subquery first and then
perform the main query. SQL must process two SELECT statements to obtain the
result.
If the select statement in the subquery produces unique results (no duplicate rows),
then the subquery can be transformed into a join. This restriction applies because even
though a subquery can have duplicate rows, each row of the outer table can produce
at most one result row. If, however, a subquery with duplicate rows is converted into a
join, then each row of the outer table can produce multiple result rows, which could
change the result set.