CREATE FOREIGN INDEX (Define a foreign index)

Function

Defines a foreign index for a column in a foreign table.

Foreign indexes are used as information for the optimization of access to a foreign table. Defining a foreign index causes the creation of definition information only in HiRDB without creating an object. This command cannot be used to define an index on a foreign server.

Privileges

Owner of a foreign table
This user can define a foreign index only for his or her own foreign tables.

Format

CREATE [UNIQUE] FOREIGN INDEX
  [authorization-identifier.]index-identifier
  on [authorization-identifier.]table-identifier
  (column-name[{ASC|DESC}][, column-name[{ASC|DESC}]]...)
  [exception-value-specification]

exception-value-specification::=EXCEPT VALUES (NULL[, NULL]...)

Operands

This operand is specified if the foreign database index is a UNIQUE-specified index.

authorization-identifier
Specifies the authorization identifier of the user who owns the foreign index that was created.
index-identifier
Specifies the name of the foreign index to be defined.
Multiple indexes and foreign indexes of the same name cannot be specified on a single authorization identifier.
authorization-identifier
Specifies the authorization identifier of the user of the foreign table for which a foreign index is to be defined.
table-identifier
Specifies the name of the foreign table for which a foreign index is to be defined.
column-name
Specifies the name of the column for which a foreign index is to be defined.
A maximum of 16 column names can be specified. If multiple column names are specified, each column name must be unique.
ASC
This operand is specified if the index on a foreign database associated with the foreign index is in ascending order.
DESC
This operand is specified if the index for the foreign database associated with the foreign index is in descending order.

This operand is specified if the index for the foreign database associated with the foreign index does not contain the null value.

An exception value cannot be specified if the index for the foreign database contains NOT NULL-constrained columns.

Common rules

  1. Foreign indexes are used as information on the optimization of access to a foreign table. For details about under which circumstances such information is to be defined, see the section on performance design for access to foreign tables in the manual HiRDB External Data Access Version 7.
  2. Exception values cannot be specified repetitively.
  3. A maximum of 255 foreign indexes can be defined per foreign table.
  4. A foreign index cannot be defined for a BINARY-type column.
  5. Only one foreign index having the same constituent columns can be defined. For single-column indexes, an ascending-order foreign index and a descending-order foreign index are considered to be the same; therefore, they cannot be defined separately. For multiple-column indexes, if the ascending or descending order column specifications are opposite for all constituent columns, they are considered to be the same.

Notes

  1. CREATE FOREIGN INDEX cannot define an index for a foreign server.
  2. CREATE FOREIGN INDEX cannot be specified from an X/Open compliant UAP running under OLTP.