SQL/MP Query Guide

Improving Query Performance Through Query
Design
HP NonStop SQL/MP Query Guide—524488-003
3-44
Specifying a Join Method
Join method does not affect the first table in a join sequence (the table that ends up as
Step 1 of the query plan). Step 1 is always a scan operation; the remaining steps are
join operations. (For more information see, Specifying a Join Sequence on page 3-45.)
If you suspect that you might benefit from the use of one of these options, check your
application with and without the CONTROL option. To check your application, run
Measure, a performance measurement tool for NonStop systems, on your production
data. This tool enables you to collect and examine performance statistics.
If you use one of the CONTROL options, 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 enhancements to SQL
•
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 method directly after the statement; for example:
CONTROL TABLE * JOIN METHOD SYSTEM
•
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.
Confirm all use of this option with data from the Measure product and verify its use
periodically to account for changes in data distributions and volumes. Reevaluate its
effectiveness with each new version of SQL.
Combining HASH JOIN and JOIN METHOD Options
If JOIN METHOD SYSTEM and CONTROL QUERY HASH JOIN OFF are both
specified, SQL never selects a hash join.
If JOIN METHOD HASH is specified, it overrides any setting of the CONTROL QUERY
HASH JOIN option for the specified table.
Caution. The JOIN METHOD 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.