SQL/MP Query Guide

Improving Query Performance Through Query
Design
HP NonStop SQL/MP Query Guide—524488-003
3-38
Determining a Join Strategy
Hash
requirements
None None None All hash joins
hash the inner
table. Hybrid
hash joins also
hash the outer
table.
Use of MDAM Can be used on
inner and outer
tables.
Can be used on
outer table.
Can be used on
outer table.
Can be used on
outer table.
Scan
requirements
Typically scans
entire inner
table for each
row of the outer
table if join
columns are not
primary key
columns or
alternate key
columns of the
inner table
Inner table
typically scanned
once, but might
be scanned once
per row of the
outer table, in
the worst case
scenario.
Inner table
scanned once.
Inner table
scanned once.
Use of join
predicates as
begin and end
keys
Can use join
predicates as
begin and end
key predicates if
join columns are
primary or
alternate key
columns of the
inner table. In
this manner,
each row of the
outer table
provides starting
and stopping
values for keyed
retrieval from
the inner table.
Can use join
predicates as
begin and end
key predicates if
join columns are
primary or
alternate key
columns of the
inner table.
Balances keyed
and sequential
retrieval. Rows
from the outer
table provide
starting values
for keyed
retrieval from the
inner table.
Cannot use join
predicates as
begin and end
keys. Must read
the entire inner
table unless
there are
additional
nonjoining key
predicates to
limit the scan.
Single pass and
sequential I/O
might, however,
still make the
hash join more
efficient.
Order of
results
Preserves
ordering of
outer table.
Produces the
join result in
order of the join
attribute, which
can eliminate a
later sort
operation.
Preserves
ordering of outer
table.
Simple join
preserves
ordering of the
outer table.
Table 3-4. Comparison of Join Strategies (page2of2)
Element of
Comparison Nested Join Sort Merge Join
Key-Sequenced
Merge Join Hash Join