Access-Method Directives

Enterprise Edition Home | Express Edition Home | Previous Page | Next Page Other Syntax Segments > Optimizer Directives >

Access-Method Directives

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

Access-Method Directives:
 
        (1)
|--+-+--------INDEX_ALL-+-(-| Table Reference |-+-----------------+-+-->
   | '-INDEX------------'                       | .-,-----------. | |
   |                                            | V             | | |
   |                                            '---+-index---+-+-' |
   |                                                '-"index"-'     |
   |                                     .-,-----------.            |
   |                                     V             |            |
   +-AVOID_INDEX--(-| Table Reference |----+-index---+-+------------+
   |                                       '-"index"-'              |
   '-+-FULL-------+--(-| Table Reference |--------------------------'
     '-AVOID_FULL-'
 
>--)--+----------+----------------------------------------------|
      '-comments-'
 
Table Reference:
 
|--+-alias---+--------------------------------------------------|
   +-synonym-+
   '-table---'

Notes:

Extended Parallel Server only

Element	Description	Restrictions	Syntax
alias	Temporary alternative table name declared in the FROM clause	If an alias is declared, it must be used in the optimizer directive	Identifier
comments	Text that documents the optimizer directive	Must be outside the parenthesis but inside the comment symbols	Character string
index	Index for which to specify a query plan directive	Must exist. With AVOID _INDEX, at least one index is required	Database Object Name
synonym, table	Synonym or table in a query for which to specify a directive	Synonym and the table to which it points must exist	Database Object Name

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 optimize.

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 indexes listed	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.
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	Access the table using all listed indexes (Multi-index scan)	If more than one index is specified, all are used. If only one is specified, optimizer follows an INDEX SKIP SCAN using that index. If no index is specified, then all available indexes are considered.

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. In addition to specifying that the optimizer not use a full-table scan, the negative directive allows the optimizer to use indexes that are created after the access-method directive is specified.

In general, you can specify only one access-method directive per table. You can, however, specify both AVOID_FULL and AVOID_INDEX for the same table. When you specify both of these 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.

Suppose that you have a table named emp that contains the following indexes: loc_no, dept_no, and job_no. When you perform a SELECT that uses the 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 dept_no)}
```
In this example the access-method directive forces the optimizer to scan the index on the dept_no column.
Example using a negative directive:
```
SELECT {+AVOID_INDEX(emp loc_no, job_no), AVOID_FULL(emp)}
```
This example includes multiple access-method directives. These access-method directives also force the optimizer to scan the index on the dept_no column. If a new index, emp_no is created for table emp, however, the optimizer can consider it.