SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-66
Left Join Transformed Into an Inner Join
The IS NULL predicate selects only the special null-augmented rows generated by the
left join operator. In this query, SQL must keep the null-augmented rows, so the left join
is necessary, and Query Rewrite does not change the left join to an inner join.
Plan step 2 shows the left join. SQL uses a hash join in this step.
The expected row count for the scan on the EMPLOYEE table is 57. For the scan on
the ORDERS table it is 1. The total cost is 6. Compare this EXPLAIN plan to the one in
Example 6-40 on page 6-67.
Left Join Transformed Into an Inner Join
Example 6-40 on page 6-67 uses the view created for Example 6-39 on page 6-65:
CREATE VIEW EMPORD AS
SELECT *
FROM EMPLOYEE E LEFT JOIN ORDERS O
ON E.EMPNUM = O.SALESREP ;
Instead of the IS NULL predicate specified in the query for Example 6-39
on
page 6-65, this query specifies an IS NOT NULL predicate:
EXPLAIN
SELECT DISTINCT LAST_NAME
FROM EMPORD
WHERE SALESREP IS NOT NULL ;
Example 6-40
on page 6-67 shows the EXPLAIN plan.
Operation 2.1 : Hash
Requested : By the optimizer
Hash rows in the : Current table
Purpose : To hash its rows before the Join
Hash key columns : O.SALESREP
Hash cost : 1
Operation 2.2 : Hash
Requested : By the optimizer
Hash rows in the : Result of a Select
Purpose : To form groups of rows for a Group By
Hash key columns : E.LAST_NAME
Expected row count: 1 after the group by
Hash cost : 1
Total cost : 6
Example 6-39. EXPLAIN Plan for Left Join Not Transformed Into an Inner
Join (page 2 of 2)