NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
C-75
CONTROL TABLE Directive
To specify different access paths for different occurrences of the same table within a
single query, use correlation names to distinguish occurrences of the table and use
the AS cor clause in CONTROL TABLE directives that specify access paths.
If you specify the INDEX option and index does not exist, SQL issues an error
(for SQLCI or dynamic SQL) or warning (for static SQL) when it compiles the
CONTROL TABLE statement. If index exists, but is not an index for the specified
table, SQL issues an error message later, when it compiles a DML statement that
references the table.
The MDAM ON option directs SQL to use MDAM for the specified table and
access path. When you specify MDAM ON, you must specify the PRIMARY or
INDEX index option; MDAM ON is not supported for ACCESS PATH SYSTEM.
You must specify MDAM ON for a specific table; you cannot use this option with
CONTROL TABLE *.
You can specify one or both of the following optional clauses with MDAM ON:
USE tells SQL how many columns of the key should be used by MDAM.
value specifies the number of columns to be used by MDAM.
value must be an integer than zero. If value exceeds the
actual number of key columns defined for the index, SQL
chooses the maximum number of key columns that can be
used by MDAM.
To direct SQL to use the first three columns of an index,
specify the following:
USE 3 KEY COLUMNS
The word KEY can be included for clarity, but has no effect.
DEFAULT directs SQL to determine the number of key columns to be
used for MDAM. You can use DEFAULT to reset a
previously-specified USE value.
ACCESS specifies whether a dense or sparse algorithm (or system-determined
algorithm) should be used when accessing columns with MDAM, as
follows:
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 additional information about access algorithms, see the NonStop
SQL/MP Query Guide.