SQL/MP Query Guide

Improving Query Performance Through Query
Design
HP NonStop SQL/MP Query Guide—524488-003
3-43
Specifying a Join Method
Specifying a Join Method
Two directives control the selection of join method:
•
The CONTROL QUERY HASH JOIN option specifies whether SQL can use hash
joins if the optimizer expects hash joins to improve query performance.
•
The CONTROL TABLE JOIN METHOD option specifies the join method SQL uses
when the specified table is the inner table of a join operation. Options include
NESTED, MERGE, KEY SEQUENCED MERGE, and HASH. If you choose not to
specify a join method, SQL selects an appropriate method for each join of the table
or tables that you specify in the CONTROL TABLE directive.
For more information on these directives, see the SQL/MP Reference Manual.
CONTROL QUERY HASH JOIN Option
A sample CONTROL QUERY HASH JOIN directive follows:
CONTROL QUERY HASH JOIN ENABLE ;
This directive ensures that SQL considers hash joins for subsequent queries.
You should usually leave the HASH JOIN option set to SYSTEM or ENABLE, because
the optimizer is designed to select a hash join only if the resulting plan improves the
performance of your query.
You might, however, choose HASH JOIN OFF if you know that memory contention is
severe in the processor or processors that run your query.
For more information, see the SQL/MP Reference Manual.
CONTROL TABLE JOIN METHOD Option
The JOIN METHOD option applies only to SELECT and INSERT-SELECT statements,
and can be specified with other CONTROL options. Sample directives are:
CONTROL TABLE EMPLOYEE JOIN METHOD NESTED ;
CONTROL TABLE * JOIN METHOD SYSTEM ;
When specifying a merge join or hash join, the query must contain an equijoin
predicate between the two tables.
If you specify JOIN METHOD HASH and the join involves columns with collations, SQL
returns an error.