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.]index-identifier
- 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.
- on [authorization-identifier.]table-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[{ASC|DESC}][, column-name[{ASC|DESC}]]...
- 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.
- exception-value-specification
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
- 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.
- Exception values cannot be specified repetitively.
- A maximum of 255 foreign indexes can be defined per foreign table.
- A foreign index cannot be defined for a BINARY-type column.
- 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
- CREATE FOREIGN INDEX cannot define an index for a foreign server.
- CREATE FOREIGN INDEX cannot be specified from an X/Open compliant UAP running under OLTP.