SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-64
Left Join Not Transformed Into an Inner Join
This plan contains two steps. Step 2 shows that the key-sequenced merge join method
was chosen. Executor aggregates were chosen because aggregates cannot be done
in DP2 on the inner table of a key-sequenced merge join.
In plan step 1, the access path is alternate, index only, sequential cache. The operation
cost is 2187.
In plan step 2, the access path is alternate, unique, index only, sequential cache. No
sort takes place. The operation cost is 1.
The total cost for the query is 4389.
Left Join Not Transformed Into an Inner Join
The EXPLAIN plans in Example 6-39 on page 6-65 and Example 6-40 on page 6-67
are for two queries that use the same view:
CREATE VIEW EMPORD AS
SELECT *
FROM EMPLOYEE E LEFT JOIN ORDERS O
ON E.EMPNUM = O.SALESREP ;
This query executes a left join that is not transformed into an inner join. It does this with
an IS NULL predicate.
EXPLAIN
SELECT DISTINCT LAST_NAME
FROM EMPORD
WHERE SALESREP IS NULL ;
The query for Example 6-40 on page 6-67 executes a left join that is transformed into
an inner join. It does this with an IS NOT NULL predicate.
In both examples, if the right table does not satisfy the search condition, SQL creates
null-augmented rows on the left table. If a WHERE or inner join predicate is certain to
eliminate all of the null-augmented rows generated by the left join, then the optimizer
transforms the left join into a more efficient inner join.
This example shows the EXPLAIN plan for the left join not transformed into an inner
join.