SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-68
EXPLAIN Plan for UNION Operation
The IS NOT NULL predicate in the query eliminates the special null-augmented rows
produced by the left join operator. The search condition retains only the joined rows
that are the same as those that result for an inner join. No performance gain results if
SQL generates null-augmented rows that will be discarded, so Query Rewrite changes
the left join to an inner join.
The EXPLAIN plan shows that plan step 2 is an inner join. The left join allows the
SALESREP column to contain nulls, so the NOT NULL predicate is unnecessary.
Therefore, Query Rewrite eliminates it. Without the left join, SQL can consider using
access plans that use ORDERS as the outer table (processed in plan step 1). As a
result, SQL uses a key-sequenced merge join.
The expected row count for the scan on the ORDERS table is 13. The expected row
counts for the scan on the EMPLOYEE table and for the hash operation are both 13.
The total expected row counts for the EXPLAIN plan in Example 6-39 on page 6-65 is
58 (57 plus 1) and the total cost is 6. In Example 6-40 on page 6-67, the total expected
row count is only 39 (13 times 3) and the total cost is 4.
EXPLAIN Plan for UNION Operation
This example shows an EXPLAIN plan for a query that includes a UNION of two
SELECT statements.
The statements retrieve data from the ordone and ordtwo tables. Both tables have a
system-defined primary key (SYSKEY).
The query follows:
EXPLAIN
SELECT * FROM ordone
UNION
SELECT * FROM ordtwo ;
The total cost of the query is 3.