In the SQL optimization specification for a used index, you can specify either an index to be used for retrieving a table or the suppression of use of an index (table scanning).

SQL-optimization-specification-for-a-used-index :: = [WITH INDEX (index-specification[, index-specification] ... |) | WITHOUT INDEX] Index-specification :: = [ [authorization-identifier.]index-identifier |PRIMARY KEY |CLUSTER KEY |PRIMARY CLUSTER KEY]

• WITH INDEX(index-specification [, index-specification] ...)

Specifies the index to be used. If multiple indexes are specified, the operation uses multiple indexes.

• WITHOUT INDEX

Suppresses the use of an index (table scanning).

• [[authorization-identifier.]index-identifier | PRIMARY KEY | CLUSTER KEY | PRIMARY CLUSTER KEY]

authorization-identifier

Specifies the authorization identifier of the user who owns the index. For default values, see 1.1.8 Qualifying a name.

index-identifier

Specifies the name of the index to be used.

PRIMARY KEY

Specifies the option of when to use an index that is defined by specifying a primary key (or a primary cluster key) during table definition. For primary and cluster keys, see CREATE TABLE.

CLUSTER KEY

Specifies the option of when to use an index that is defined by specifying an index key (or a primary cluster key) during table definition.

PRIMARY CLUSTER KEY

Specifies the option of when to use an index that is defined by specifying a primary cluster key during table definition.

1. SQL optimization cannot be specified for a named derived table that functions as an inner derived table. For inner derived tables, see 2.21 Inner derived tables.
2. SQL optimization cannot be specified for named derived tables that are derived from the joining of two or more tables or as a result of a set operation.
3. If additional SQL optimization for a used index is specified in a named derived table that is derived by specifying SQL optimization for a used index, the later specification takes effect, and the specification of the derived query expression is nullified.
4. When specified for an outer table, any SQL optimization is nullified.

1. For retrievals using an index, see the retrieval methods described in the HiRDB Version 8 UAP Development Guide. Indexes that are used can be checked by using the access path display utility.