SQL/MP Query Guide

Improving Query Performance Through Query
Design
HP NonStop SQL/MP Query Guide—524488-003
3-29
Hash Join
The key-sequenced merge join method differs from a nested join in the way that
records are retrieved from both the outer and inner tables. The key-sequenced merge
join method may read several rows on the outer table before retrieving a row from the
inner table. The nested join method reads only one row from the outer table before
accessing the inner table.
A key-sequenced merge join uses virtual sequential block buffering (VSBB) when it
sequentially retrieves rows from the inner table. A nested join randomly accesses rows
on the inner table, using separate retrievals for each outer row.
Even though the key-sequenced merge join method can reposition and reread rows on
the inner table, this method can still be more efficient than the nested join. When the
key-sequenced merge join uses VSBB, it sets the end key to the maximum possible
value. As a result, more data can be in the buffer than is actually needed. On
subsequent retrievals, the data will already be in the buffer. This result is not true for
the VSBB used in the nested join. For more information on VSBB and the nested join,
see Nested Join on page 3-25. For information on VSBB, see Section 4, Improving
Query Performance With Environmental Options.
The DISPLAY STATISTICS for a key-sequenced merge join can be misleading
because the counts for records accessed and for records used can be greater than
those in a nested join.
For information on forcing a key-sequenced merge join, see the SQL/MP Reference
Manual. Also, see Specifying a Join Method on page 3-43.
Hash Join
Hash methods eliminate sort operations for a join operation. The hash-join strategy is
considered an asymmetric algorithm because only the inner table is stored in memory.
Hashing is most efficient when the inner table can fit entirely into memory and is ideal
for joining a large table with a small table when the joining columns are not key
columns. The size of a large outer table does not influence the amount of memory
needed—an advantage over sort merge joins, which sometimes must sort the large
table.
Figure 3-3. Key-Sequenced Merge Join
Key-sequenced
merge join result
Outer table:
One sequential
pass
Inner table :
Sequential
acc ess
0
29
VST0303.vsd