2.24.1 SQL optimization specification for a used index

Organization of this subsection
(1) Function
(2) Format
(3) Operands
(4) Rules
(5) Notes

(1) Function

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

(2) Format

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]

(3) Operands

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

Suppresses the use of an index (table scanning).

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.

(4) Rules

  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.

(5) Notes

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