SQL/MP Query Guide

Improving Query Performance Through Query
Design
HP NonStop SQL/MP Query Guide—524488-003
3-28
Key-Sequenced Merge Join
The read process for the key-sequenced merge join is the same as that for the sort
merge join, except for the third step. A row is retrieved from the outer table, another
row is retrieved from the inner table, and the values of the join columns for the two
rows are compared as follows:
1. If the values are the same, the rows are concatenated, projected, and returned to
the user, and the position of this inner row is stored in memory.
2. The next inner row is retrieved and the process is repeated until the join-column
values of the inner and outer table rows are different.
3. The next row is then retrieved from the outer table:
a. If the join-column value is the same as that in the previous outer row, the inner
table is restored to its original position, and the process is repeated from Step
1.
b. If the join-column value of the inner row is less than that of the outer row, then
the following occurs:
1. A limited number of rows are read until the value of the inner row is greater
than or equal to that of the outer row. (The optimizer chooses the limit.)
2. If the limit is reached before SQL finds such an inner row, the inner table
rows are skipped and the outer row value is then used as an index into the
inner table.
c. If the join-column value of the inner row is greater than that of the outer row,
the next outer row is retrieved until the outer row has a value greater than or
equal to that of the inner row.
d. If the join column values of the outer row and inner row are equal, then the
process is repeated from Step 1.
When a left join uses a key-sequenced merge join, a null-augmented row is created for
the inner table when no match occurs. Then a random position takes place on the
inner table, using the next new outer row key.