SQL/MP Query Guide

Improving Query Performance Through Query
Design
HP NonStop SQL/MP Query Guide—524488-003
3-35
Determining a Join Strategy
DEPT_NUM column. The DEPT table has DEPT_NUM as the primary key. this query
asks for employee and department information:
SELECT EMP_NAME, DEPT_NAME, SALARY
FROM EMPLOYEE, DEPT
WHERE EMPLOYEE.DEPT_NUM = DEPT.DEPT_NUM ;
The information is retrieved by joining the EMPLOYEE and DEPT tables. There are
several ways to join the tables; for example:
•
(EMPLOYEE with primary key JOIN DEPT)
•
(EMPLOYEE with index JOIN DEPT)
•
(DEPT JOIN EMPLOYEE with primary key)
•
(DEPT JOIN EMPLOYEE with index)
The order in which the rows are presented depends on the access path used for the
outer table. For choice 1, the rows are presented in EMP_NAME order. For choices 2,
3, and 4, the rows are presented in DEPT order.
If another table, JOB, is to be joined with the preceding result table, SQL considers
only the composite from choice 1 or the composite from the least expensive of choices
2, 3, and 4. This strategy reduces the number of combinations to be joined with JOB
from four to two. In general, SQL discards all but the least expensive of the
combinations for a given order.
Combining Tables for Hash Join Operations
When evaluating a hash join operation, SQL attempts to choose the smaller of two
tables as the inner table so the table is more likely to fit into memory.
Forming Cross Products
If you specify multiple tables in the FROM clause without a join predicate or with a join
predicate that is always true, SQL forms a Cartesian product (or cross product) by
concatenating each row of each table with every row of every other table. This strategy
produces a set of composite rows that contains all possible concatenations of a row
from the first table with a row from the second table.
SQL performs this operation whenever there is a join without an equality predicate
between a table or composite table and another table. For queries with several tables,
the process might be repeated several times.
A Cartesian product involving two tables, one of size M and the other of size N, is of
size M x N. If the tables are large, the performance overhead can be quite costly.
Therefore, in most situations, SQL does not form a Cartesian product unless a single
table does not have a join predicate connecting it to the composite table. If none of the
remaining single tables has a join predicate connecting it to the composite, then the
optimizer considers a Cartesian product between the composite and each of the
remaining tables.