SQL/MP Query Guide

Improving Query Performance Through Query
Design
HP NonStop SQL/MP Query Guide—524488-003
3-36
Determining a Join Strategy
In some situations, SQL forms a single Cartesian product that significantly reduces the
number of rows to be joined.
Consider this example that creates three tables and then selects data from the tables:
CREATE TABLE buildng (a INT, m INT, n INT, KEY a) ;
CREATE TABLE room (x INT, b INT, KEY x) ;
CREATE TABLE locatn (c INT, d INT, e INT, f INT, KEY (c,d)) ;
SELECT * from buildng, room, locatn
WHERE buildng.a, room.b = locatn.c,locatn.d
AND room.b = 5
AND buildng.m > 20 ;
Suppose that the predicates produce these results:
•
buildng.m > 20 produces 100 rows
•
room.b = 5 produces 10 rows
•
The first key of locatn produces 1,000 rows for each equal relation, but both keys
(c and d) yield exactly one row
Without an intermediate cross product, SQL would join buildng to locatn (as in
buildng.a = locatn.c) and then join the composite with room. The join between
the composite and room would involve 100,000 rows.
With a cross product between buildng and room, however, before the join with locatn,
only 1,000 rows remain after the cross product and only 1,000 rows are read from table
locatn, resulting in a substantial reduction in query time.
SQL forms Cartesian products for both serial and parallel execution plans; for parallel
plans, browse access or TABLE LOCK ON must be specified. For more information,
see Section 4, Improving Query Performance With Environmental Options and the
SQL/MP Reference Manual.
Relative Performance of Join Strategies
For each two-way join, SQL considers four join methods: nested, sort merge, key-
sequenced merge, or hash.
If M and N are the number of rows in two joined tables, then the increasing resource
cost of joins is roughly calculated as shown in Table 3-3.
Table 3-3. Calculation of Resource Costs for Joins
Join Strategy Order of Cost
Nested Join M * N, with no indexes
Sort Merge Join N log N
Key-Sequenced Merge Join N
Hash Join N, with a minimum amount of memory