NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
C-76
CONTROL TABLE Directive
If you specify both USE and ACCESS, the USE option must precede the ACCESS
option. Otherwise, SQL returns an error. If you specify MDAM ON along with
CONTROL QUERY MDAM OFF, SQL returns an error.
When you specify the ACCESS PATH...MDAM ON option, SQL resets any
unspecified options to their default values. If, for example, you change the value of
the USE option and want to preserve a previous ACCESS setting, include the
appropriate ACCESS clause in your new CONTROL TABLE statement.
JOIN METHOD { NESTED | [KEY SEQUENCED] MERGE | HASH | SYSTEM }
specifies whether to use the nested, sort merge, key-sequenced merge, or hash join
method when the specified table is the inner table of a join operation. (See the
NonStop SQL/MP Query Guide for descriptions of these join methods.)
JOIN METHOD SYSTEM is the default. SYSTEM directs SQL to select an
appropriate join method for each join of the table or tables specified in the
CONTROL TABLE directive.
If a CONTROL QUERY HASH JOIN OFF directive is in effect, JOIN METHOD
SYSTEM never selects a hash join. If you explicitly request JOIN METHOD
HASH, however, that CONTROL TABLE directive overrides the CONTROL
QUERY directive for the specified table.
SQL cannot perform a hash join on a column or index that has an associated
collation. If you specify JOIN METHOD HASH in such a case, SQL returns
error -6021.
The KEY SEQUENCED MERGE option applies only to candidates for a key-
sequenced merge join operation. If the current composite table and the inner table
are not in the same order (such as in a repartitioning parallel plan), SQL returns an
error and does not choose the key-sequenced merge join method.
Because JOIN METHOD applies only to the inner table of a join operation, it is
ignored for a table that has a join sequence of 1. JOIN METHOD is normally used
only in conjunction with the JOIN SEQUENCE option.
JOIN SEQUENCE { SYSTEM | sequence-number }
specifies the sequence in which SQL processes the table for the join.
JOIN SEQUENCE SYSTEM is the default; it directs SQL to determine the
sequence.
sequence-number is an integer that specifies the join sequence for the table
within a SELECT. For example, if you specify sequence-number as 3 for a
table, that table is third in the join sequence for a SELECT.
You do not need to specify sequence-number for each table in a SELECT; SQL
determines the join sequence for tables you do not mention in CONTROL TABLE
directives. For example, if you specify sequence-number as 3 for a table, but
have no CONTROL TABLE directives in effect for other tables, SQL determines
the tables that are first and second in the join order, as well as those that are fourth,
fifth, and so forth.