SQL/MP Query Guide

Improving Query Performance Through Query
Design
HP NonStop SQL/MP Query Guide—524488-003
3-34
Determining a Join Strategy
Determining a Join Strategy
When evaluating join methods, the optimizer looks at ways to join tables, both with
structure and ordering of combinations of tables and with various join strategies. The
goal is to minimize processor time, disk access, sorts, and other resource use so that
resource consumption is minimized, and performance is as fast as possible.
The optimizer evaluates performance for each access path to each table.
The optimizer uses a cost model to evaluate join strategies and chooses the strategy
with the lowest estimated cost. (For information about cost, see Section 5, Selectivity
and Cost Estimates. To list the chosen strategy, see EXPLAIN output, described in
Section 6, Analyzing Query Performance.)
Combining Tables
When two tables are joined, SQL forms a composite table. For example, if two tables,
T1 and T2, are joined, SQL forms a composite table, T1 JOIN T2, in the course of
evaluating the query.
The number of different ways to join a set of tables increases exponentially as the
number of tables increases, so SQL reduces the number of possibilities when possible.
When joining more than two tables, SQL follows these steps:
1. Joins tables a pair at a time. SQL considers only two-way joins that involve either
two tables or a composite table and a table that does not already belong to the
composite.
2. Evaluates joins between the composite table and all single tables that have not yet
been added to the composite. For each single table, associate all join predicates
that relate the single table with tables that are already in the composite.
3. Generates plans for types of join strategies (nested, sort merge, key-sequenced
merge, and hashed).
4. When the composite table contains all tables that need to be joined, chooses the
plan with the least cost. (For information about cost, see Section 5, Selectivity and
Cost Estimates.)
This pairwise strategy reduces the number of combinations that must be examined and
simplifies evaluation. For example, if tables T1, T2, T3, and T4 are to be joined, this
combination is considered:
(((T1 JOIN T2) JOIN T3) JOIN T4)
This combination is not considered, because it joins two composite tables:
((T1 JOIN T2) JOIN (T3 JOIN T4))
The number of combinations is also reduced by discarding more expensive
combinations that give the same order to result rows. For example, suppose that the
EMPLOYEE table has EMP_NAME as the primary key and an index exists on the