SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
C-80
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.
If you define a table with a clustering key and use a protection view, you will not be
able to use MDAM. For more information, see Protection View on page P-32.
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. (For
descriptions of these join methods, see the SQL/MP Query Guide.)
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
USE communicates to SQL how many columns of the key should be used by
MDAM.
DENSE specifies an adaptive dense algorithm for all columns unless
SQL determines that a dense algorithm is not appropriate, as
with character or float data types. DENSE is the preferred
algorithm when column values are generally sequential. SQL
increments each value to obtain the next value.
SPARSE specifies a sparse algorithm. This is the preferred algorithm
when column values are not sequential (such as 25, 135, and
500). SQL uses positioning to obtain the next value of the
column.
SYSTEM specifies that SQL determines the type of algorithm for each
column. You can use SYSTEM to reset a previously specified
ACCESS option.
For more information about access algorithms, see the SQL/MP Query
Guide.