SQL/MP Query Guide

Improving Query Performance Through Query
Design
HP NonStop SQL/MP Query Guide—524488-003
3-27
Key-Sequenced Merge Join
For information on forcing a merge join, see the SQL/MP Reference Manual. Also, see
Specifying a Join Method on page 3-43.
Key-Sequenced Merge Join
The key-sequenced merge join method can apply a merge-join method to tables
without doing a sort or using a temporary file. A key-sequenced merge join involves an
outer composite and an inner table. An outer composite is either a single outer table or
the result of any joins that have occurred so far. The operator in the join predicate must
be an equal (=) operator, and one of these conditions must be met:
•
The tables must be in the same order.
•
If rows from the inner table must be accessed by index, then the outer table of the
join must be in the same sequence as one of the indexes of the inner table.
The key-sequenced merge join has two advantages over a sort merge join: it does not
require a sort on one or both tables and it is not limited to a single column.
Figure 3-2. Sort Merge Join
Sort-merge join result
Outer table:
One seque ntial
pass
Inner table:
One sequential
pass
0
28
Sort
Only if
ou te r ta ble is
not in sorted
order
Temporary
file
Sort
Temporary
file
VST0302.vsd