Access-method directives

The database server uses an access method to access a table. The server can either read the table sequentially via a full table scan or use any one of the indexes on the table. Access-method directives influence the access method.

The following table lists the directives that influence the access method:

Access-Method Directive Description
INDEX Tells the optimizer to use the index specified to access the table. If the directive lists more than one index, the optimizer chooses the index that yields the least cost.
AVOID_INDEX Tells the optimizer not use any of the indexes listed. You can use this directive with the AVOID_FULL directive.
INDEX_SJ Forces an index self-join path using the specified index, or choosing the least costly index in a list of indexes, even if data distribution statistics are not available for the leading index key columns of the index.

For information about index self-join paths, see Query plans that include an index self-join path.
AVOID_INDEX_SJ Tells the optimizer not to use an index self-join path for the specified index or indexes.
FULL Tells the optimizer to perform a full table scan.
AVOID_FULL Tells the optimizer not to perform a full table scan on the listed table. You can use this directive with the AVOID_INDEX directive.
INDEX_ALL or MULTI_INDEX Access the table by using the specified indexes for a multi-index scan.

The INDEX_ALL and MULTI_INDEX keywords are synonyms.
AVOID_MULTI_INDEX Tells the optimizer not to consider a multi-index scan path for the specified table.

In some cases, forcing an access method can change the join method that the optimizer chooses. For example, if you exclude the use of an index with the AVOID_INDEX directive, the optimizer might choose a hash join instead of a nested-loop join.

The optimizer considers an index self-join path only if all of the following conditions are met:
  • The index does not have functional keys, user-defined types, built-in opaque types, or non-B-tree indexes
  • Data distribution statistics are available for the index key column under consideration
  • The number of rows in the table is at least 10 times the number of unique combinations of all possible lead-key column values.

If all of these conditions are met, the optimizer estimates the cost of an index self-join path and compares it with the costs of alternative access methods. The optimizer then picks the best access method for the table. For more information about the access-method directives and some examples of their usage, see the HCL OneDB™ Guide to SQL: Syntax.