SQL/MP Query Guide

Improving Query Performance Through Query
Design
HP NonStop SQL/MP Query Guide—524488-003
3-26
Sort Merge Join
predicates, it chooses the column with the lowest selectivity. That is, it picks the
column that it expects to have the fewest matches between values in the inner and
outer table rows.
If the outer table is not already sorted on the joining column, the table is sorted into a
temporary entry-sequenced table. The join is done between the temporary sorted inner
table and the outer table.
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, the
next inner row is retrieved until the value of the inner row is greater than or
equal to that of the outer row.
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.
SQL makes one pass through the inner table, with possible limited looping within a
value range. The performance difference between a nested join and a sort merge join
is the difference between random access with possible repeated access to the same
pages needed for a nested join and the cost of sort operations needed for the sort
merge join.
Steps 1 through 3 are repeated until all rows from the outer table have been examined.