SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
C-79
CONTROL TABLE Directive
ACCESS PATH { SYSTEM | PRIMARY | INDEX index }
[ MDAM ON [ USE {value|DEFAULT} [KEY] COLUMN[S] ]
[ ACCESS { SPARED | DENSE| SYSTEM} ] ]
controls the access path for a DELETE, SELECT, or UPDATE, or for the SELECT
portion of an INSERT-SELECT.
The default is ACCESS PATH SYSTEM.
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 these optional clauses with MDAM ON:
SYSTEM directs SQL to choose the access path
PRIMARY specifies the primary access path for the table
INDEX specifies index as the access path
USE communicates to 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 this:
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: