SQL/MP Query Guide

Improving Query Performance Through Query
Design
HP NonStop SQL/MP Query Guide—524488-003
3-37
Determining a Join Strategy
For each join performed within the join strategy listed in the “Join Strategy” column, the
cost increases by the algorithm shown in the “Order of Cost” column. Various factors
influence these general rules.
Table 3-4 compares join strategies.
Table 3-4. Comparison of Join Strategies (page1of2)
Element of
Comparison Nested Join Sort Merge Join
Key-Sequenced
Merge Join Hash Join
Requirements
for join
strategy
None Query must
include an
equality
operation as part
of the join
predicate.
Query must
include an
equality
operation as part
of the join
predicate. Join
predicate must
include the
leading key
columns of the
inner table. Inner
and outer tables
must be in the
same order.
Query must
include an
equality
operation as part
of the join
predicate.
Processor
overhead
If no indexes
are available,
cost increases
with the number
of rows.
Cost increases
by approximately
n log n with the
number of rows
in the smaller
table.
Cost increases
by approximately
n, with the
number of rows
in the larger
table.
Cost increases
by approximately
n, with the
number of rows
in the smaller
table.
Sort
requirements
None Always requires
one sort for the
inner table. If
values in the
joining columns
are not in the
same ascending
or descending
sequence, the
optimizer might
choose to sort
the outer table
as well and then
perform the sort
merge join.
Does not require
a sorting
operation. This
can be
especially helpful
if a sort is not
needed for an
ORDER BY or
GROUP BY
operation in the
query.
Does not require
a sorting
operation. This
can be
especially helpful
if a sort is not
needed for an
ORDER BY or
GROUP BY
operation in the
query.