CREATE INDEX Format 2 (Define index)

Function

CREATE INDEX (format 2) defines an index of a specified index type.

Privileges

Owner of the table

A user can define indexes in public user RDAREAs for tables owned by that user.

Table owner who has the usage privilege for private user RDAREAs

A user can define indexes for tables owned by that user in private user RDAREAs for which the user has the privilege.

Format 2 (Define index of a specified index type)

CREATE INDEX [authorization-identifier.] index-identifier
   USING TYPE [authorization-identifier.] index-type-identifier
   ON [authorization-identifier.] table-identifier (column-name)
   IN {RDAREA-name
       |(RDAREA-name)
       |((RDAREA-name)[, (RDAREA-name)]...)
       |matrix-partitioned-index-storage-RDAREA-specification}
    [index-option]...
    [PLUGIN plug-in-option]
matrix-partitioned-index-storage-RDAREA-specification::=
   two-dimensional-storage-RDAREA-specification
two-dimensional-storage-RDAREA-specification::=
   (matrix-partitioning-RDAREA-list
     [, matrix-partitioning-RDAREA-list]...)
matrix-partitioning-RDAREA-list::=
   (RDAREA-name[, RDAREA-name]...)
index-option ::= EMPTY

Operands

Specifies the authorization identifier of the user who will own the created index and specifies a name for the index.

The index name cannot be the same as the table name.

authorization-identifier
Specifies the authorization identifier of the owner of the index type.
When the authorization identifier is omitted, the default authorization identifier does not include an index type of the same name, and there is an index type of the same name in the 'MASTER' authorization identifier, that index type is assumed to have been specified.
index-type-identifier
Specifies the index type. For the index type identifiers, see the manual for the appropriate plug-in.
authorization-identifier
Specifies the authorization identifier of the user who will own the created table.
table-identifier
Specifies the name of the base table on which the created index is to be based.
FIX tables cannot be specified.
column-name
Specifies the name of the column for which the index is being defined. The only permissible data type for the column is an abstract data type.

Specifies the name of the RDAREA for storing indexes.

When defining an index for a matrix-partitioned table, specify a matrix-partitioned index storage RDAREA.

The following rules apply to RDAREAs that store an index:

  1. The RDAREA name must be a user LOB RDAREA.
  2. The RDAREA must be created or added in advance by using either the database initialization utility or the database structure modification utility, respectively.
  3. When specifying more than one RDAREA name, you cannot specify the same RDAREA name. However, if there are duplicate table storage RDAREA names in a partitioned table with a boundary value specification or in a matrix-partitioned table, duplicate RDAREA names can be specified.
  4. If a table is stored in multiple RDAREAs on a partitioned basis, index storage RDAREAs can be specified as follows:
    • Specify RDAREA names in a number equal to the RDAREAs storing tables. In this case, the targets of index storage correspond to the order in which table storage RDAREAs are specified in CREATE TABLE.
    • If there are duplicate table storage RDAREA names in a partitioned table with a boundary value specification or in a matrix-partitioned table, specify corresponding index storage RDAREA names.

Specifies that an empty plug-in index is being created.

The EMPTY option improve the capacity for concurrent execution of plug-in index definitions. This option is also effective when a table contains a large amount of data and the definitions of multiple indexes must be executed concurrently. This option is not effective for tables that do not contain data.

For details of using the EMPTY option, see the HiRDB Version 8 System Operation Guide.

Specifies as a character string literal (of up to 255 characters) parameter information for the plug-in index. Hexadecimal character string literals cannot be specified as parameter information. For details about the parameter information, see the documentation for the appropriate plug-in.

Common rules

  1. A maximum of 255 indexes can be defined per table.
  2. An index can be defined for a column that contains NULL values as well as for a column that does not contain any rows.
  3. RDAREAs using the inner replica facility and those not using the facility cannot be specified on a mixed basis in the index storage RDAREAs. When specifying an RDAREA to which the inner replica facility is applied, specify the name of the original RDAREA.
  4. For execution conditions for CREATE INDEX using the inner replica facility, see the manual HiRDB Staticizer Option Version 7.
  5. A maximum of 500 indexes can be stored per RDAREA.

Notes

  1. When a value in an indexed column is updated by a user, the index is also updated.
  2. The CREATE INDEX statement cannot be specified from an X/Open-compliant UAP running under OLTP.
  3. A plug-in index that is defined with the EMPTY option specified must be re-created by the database reorganization utility. For details of re-creating an index, see the manual HiRDB Version 8 Command Reference.