SQL/MP Query Guide

Improving Query Performance Through Query
Design
HP NonStop SQL/MP Query Guide—524488-003
3-45
Specifying a Join Sequence
Specifying a Join Sequence
The CONTROL TABLE JOIN SEQUENCE option controls the order in which tables are
combined in a join operation. You specify access by defining an ordinal position for a
table. The statement accepts integers; the table associated with the number one is the
first table processed. For example, JOIN SEQUENCE 1 forces SQL to assign the
specified table to the first step of the join operation (the outermost loop).
The JOIN SEQUENCE option applies only to SELECT and INSERT-SELECT
statements and can be specified with other CONTROL options. A sample directive
follows:
CONTROL TABLE EMPLOYEE JOIN SEQUENCE SYSTEM ;
If you specify the SYSTEM option, SQL chooses the join sequence SYSTEM (default)
option. If, for example, you specified CONTROL TABLE EMP1 JOIN SEQUENCE 1 in
an SQLCI session, use CONTROL TABLE EMP1 JOIN SEQUENCE SYSTEM (for
table EMP1) or CONTROL TABLE * JOIN SEQUENCE SYSTEM (for all tables in
subsequent queries) to restore the default join sequence mechanism.
If you suspect that you might benefit from the use of one of these options, check your
application with and without the CONTROL option, using actual Measure statistics from
production data.
You might want to change this directive later for reasons such as these:
•
The query might not be able to use a more efficient index that might be created in
the future
•
The query might not be able to benefit from future SQL enhancements
•
Changes to the database structure (such as dropping an index) can require
recompilation when the option is in use
Therefore, make any occurrences of it easy to find and change, using one or more of
these alternatives:
•
Make sure the directive only applies to the statement and table intended. Return
the specified table to SYSTEM sequence directly after the statement.
•
Isolate this directive in its own section and perform it from the inline application
code.
•
Place all statements affected by this directive in separate modules, called as
services by other modules.
Caution. The JOIN SEQUENCE option overrides the optimizer’s standard cost estimates
(described in Section 5, Selectivity and Cost Estimates) and therefore might cause
performance degradation instead of enhancement. To use this option, you must have a
thorough understanding of the optimizer. Use it only if the optimizer does not produce the
optimal plan.