Access-Method Directives

Use the access-method directives to specify the manner in which the optimizer should search the tables.

Table Reference


Element	Description	Restrictions	Syntax
alias	Temporary alternative table name declared in the FROM clause	If an alias is declared, it must be used (rather than table or synonym)	Identifier
comments	Optional text that documents the directive	Must be outside the parentheses but inside the comment symbols	Character string
index	Index for which to specify the directive	Must exist. With AVOID_INDEX, AVOID_INDEX_SJ, and INDEX_SJ, at least one index is required	Identifier
synonym, table	Name or synonym of a table to which the directive applies	Synonym and the table to which it points must exist	Identifier

Use commas or blank spaces to separate elements within the parentheses.

The following table describes each of the access-method directives and indicates how it affects the query plan of the optimizer.


Keywords	Effect	Optimizer Action
AVOID_FULL	No full-table scan on the listed table	The optimizer considers the various indexes it can scan. If no index exists, the optimizer performs a full-table scan.
AVOID_INDEX	Does not use any of the specified indexes	The optimizer considers the remaining indexes and a full-table scan. If all indexes for a table are specified, optimizer uses a full-table scan to access the table.
AVOID_INDEX_SJ	Does not use an index self-join path for the specified indexes	The optimizer does not consider the specified index for scanning the table in an index self-join path.
AVOID_MULTI_INDEX	Does not use a multi-index scan path for the specified table	The optimizer does not consider a multi-index scan path for the specified table.
FULL	Performs a full-table scan	Even if an index exists on a column, the optimizer uses a full-table scan to access the table.
INDEX	Uses the index specified to access the table	If more than one index is specified, the optimizer chooses the index that yields the least cost. If no indexes are specified, then all the available indexes are considered.
INDEX_ALL or MULTI_INDEX	Access the table using the specified indexes (Multi-index scan)	These keywords are synonyms. For usage information, see "Multi-index scans" below.
INDEX_SJ	Use the specified index to scan the table in an index self-join path.	The optimizer is forced to scan the table using an index self-join path with the specified index (or to choose the least costly index in a list of indexes for an index self-join path).

Both the AVOID_FULL and INDEX keywords specify that the optimizer should avoid a full scan of a table. It is recommended, however, that you use the AVOID_FULL keyword to specify the intent to avoid a full scan on the table.

The AVOID_MULTI_INDEX directive does not accept a list of indexes as its argument. This is because the AVOID_INDEX directive also prevents the specified index from being used in a multi-index scan execution path.

Multi-index scans

Up to sixteen (16) indexes can be defined on a table. A search path based on an access method that uses more than one index on the same table is called a multi-index scan. The MULTI_INDEX or INDEX_ALL directive forces the query optimizer to consider a multi-index scan to search the specified table for qualifying rows. The argument list for the MULTI_INDEX or INDEX_ALL directive has these semantics:

If you specify a table as the only argument to the directive, the optimizer considers all of the available indexes on that table, and uses all of them (or a subset) when it searches the table for qualifying rows.
If you specify a table and only a single index, the optimizer considers using only that index to scan the table.
If you specify a table and more than one index, the optimizer considers a search path that uses all of the specified indexes.

Multi-index scan with skip-scan access methods

A multi-index scan path accesses a table by a skip-scan access method, using a sorted list of ROWIDs. The sorted list is typically generated from a multi-index scan access method, using all of the indexes that the INDEX_ALL or MULTI_INDEX directive specifies.

For example, if the query predicates specify col1 <= 10 and col2 BETWEEN 15 AND 25, then the execution plan can use two indexes: the first index on col1, and the second index on col2. Each index scan returns all ROWIDs that satisfy the search condition for the respective index. The logical intersection of the two lists of ROWIDs includes only the rows that satisfy both search conditions. The database server then sorts the combined ROWID list, and uses this sorted list to scan the table for the result set of the query.

If the query includes predicates on more than two indexed columns, the list of ROWIDs that each index scan returns must be combined to produce a sorted ROWID list of all the qualifying rows.

Because each ROWID represents the physical location of a row (on which page and in which slot), the execution path simply accesses that physical location to retrieve the row. As the term "skip-scan" suggests, there are typically gaps from one ROWID to the next in the sorted list, so that the database server "skips" from one qualifying row to the next qualifying row of the result set.

The list of sorted ROWIDs can be generated from multiple index scans, as described above, or from a single index scan. In the case of a single index, the skip-scan execution path takes these actions:

The single index scan creates an unsorted list of the ROWIDs of all qualifying rows.
This unsorted list is sorted by ROWID value.
The database server then retrieves the qualifying rows in the order of their ROWIDs.

A skip-scan access method resembles a sequential scan, but can sometimes be more efficient. A sequential scan retrieves every row in the table, but a skip-scan only retrieves the rows that have qualifying ROWIDs.

Restrictions on multi-index scan paths for query execution

The transaction isolation level affects whether the MULTI_INDEX or INDEX_ALL directive can force a multi-index scan execution path, which is not available while the isolation level is Cursor Stability, or is Committed Read with the LAST COMMITTED option. (This directive is supported, however, in the Dirty Read and Repeatable Read isolation levels, and in Committed Read without the LAST COMMITTED option.)

The following additional restrictions apply to multi-index scan access paths:

The indexes must be B-tree indexes. These can be attached or detached indexes.
These directives are ignored for R-tree indexes, functional indexes, and indexes based on the Virtual Index Interface (VII).
The table cannot be a remote table, a pseudo-table, a system catalog table, an external table, or a hierarchical table.
A multi-index scan cannot support join predicates as index filters in the underlying index scans.
A multi-index scan ignores all columns of a composite index except the leading column.
DML statements that perform cascade deletes or declare statement local variables (SLVs) cannot use a multi-index scan.
Update queries that activate a FOR EACH ROW triggered action cannot use a multi-index scan.
In ANSI-compliant databases, the MULTI_INDEX or INDEX_ALL directive is not followed for a SELECT statement that has no ORDER BY clause, no GROUP BY clause, and no FOR READ ONLY clause, if the FROM clause specifies only a single table. (In this special case, the query has implicit cursor behavior that conflicts with a multi-index scan access path.)

Combinations of access method directives

In general, you can specify only one access-method directive per table. Only the following combinations of access-method directives are valid for the same table in the same query:

INDEX, AVOID_INDEX_SJ
AVOID_FULL, AVOID_INDEX
AVOID_FULL, AVOID_INDEX_SJ
AVOID_INDEX, AVOID_INDEX_SJ
AVOID_FULL, AVOID_INDEX, AVOID_INDEX_SJ
AVOID_FULL, AVOID_MULTI_INDEX
AVOID_INDEX, AVOID_MULTI_INDEX
AVOID_INDEX_SJ, AVOID_MULTI_INDEX
AVOID_FULL, AVOID_INDEX_SJ, AVOID_MULTI_INDEX
AVOID_INDEX, AVOID_INDEX_SJ, AVOID_MULTI_INDEX

When you specify both the AVOID_FULL and AVOID_INDEX access-method directives, the optimizer avoids performing a full scan of the table and it avoids using the specified index or indexes. This combination of negative directives allows the optimizer to use indexes that are created after the access-method directives are specified.

Because the optimizer automatically considers the index self-join path if you specify the INDEX or AVOID_FULL directive, use the INDEX_SJ directive only to force an index self-join path using the specified index (or choosing the least costly index in a comma-separated list of indexes). The INDEX_SJ directive can improve performance when a multicolumn index includes columns that provide only low selectivity as index key filters.

Specifying the INDEX_SJ directive circumvents the usual optimizer requirement for data distribution statistics on the lead keys of the index. This directive causes the optimizer to consider an index self-join path, even if data distribution statistics are not available for the leading index key columns. In this case, the optimizer only includes the minimum number of index key columns as lead keys to satisfy the directive.

For example, if an index is defined on columns c1, c2, c3, c4, and the query specifies filters on all four of these columns but no data distributions are available on any column, then specifying INDEX_SJ on this index will result in column c1 being used as the lead key in an index self-join path. If you want the optimizer to use an index but not to consider the index self-join path, then you must specify an INDEX or AVOID_FULL directive to choose the index, and you must also specify an AVOID_INDEX_SJ directive to prevent the optimizer from considering any other index self-join path.

If AVOID_INDEX_SJ is used together with the INDEX directive, either as an explicit INDEX directive or as the equivalent AVOID_FULL and AVOID_INDEX combination, the indexes specified in the AVOID_INDEX_SJ directive must be a subset of the indexes specified in the INDEX directive. For more information about the effects of the INDEX_SJ and AVOID_INDEX_SJ directives, see the chapter of theHCL OneDB™ Performance Guide that describes optimizer directives.

Specifying the MULTI_INDEX or INDEX_ALL directive circumvents the usual optimizer requirement for statistics on the specified table. The optimizer normally requires at least low level statistics on the table before considering multi-index scan path on the table.

Examples of Access Method Directives

Suppose that you have a table named emp that contains the columns emp_no, dept_no, and job_no, and for which the following indexes ids_dept_no index is defined on the dept_no column, and the idx_job_no index is defined on the job_no column. When you perform a SELECT that includes the emp table in the FROM clause, you might direct the optimizer to access the table in one of the following ways:

Example using a positive directive:
```
SELECT {+INDEX(emp idx_dept_no)} ...
```
In the example above, the access-method directive forces the optimizer to consider an execution path that scans the idx_dept_no index on the dept_no column.

In the following example the access-method directive forces the optimizer to consider using a multi-index scan, based on the combined results of scanning both the idx_dept_no index on the dept_no column and the idx_job_no index on the job_no column.
```
SELECT {+MULTI_INDEX(emp idx_dept_no ids_job_no)} ...
```
Example using negative directives:
```
SELECT {+AVOID_INDEX(emp idx_loc_no, idx_job_no), AVOID_FULL(emp)} ...
```
This example includes multiple access-method directives. These directives force a scan of the idx_dept_no index on the dept_no column by instructing the optimizer not to scan the idx_loc_no and idx_job_no indexes, and not to perform a full scan of the emp table. If a new idx_emp_no index, however, is created for table emp, these directives do not prevent the optimizer from considering it.

Note also that the term negative directive refers to the string "AVOID_" in an access method directive, and has nothing to do with the + symbol following the comment indicator that begins every optimizer directive.