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. The same RDAREA name cannot be used when specifying multiple RDAREAs. However, duplicate table storage RDAREA names can be specified in row-partitioned tables, hash-partitioned tables, and matrix-partitioned tables that specify a boundary value.
  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 a table storage RDAREA name is duplicated in a row-partitioned table, hash-partitioned table, or matrix-partitioned table that specifies a boundary value, specify the index storage RDAREA name so that it corresponds.

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 9 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 Version 9 Staticizer Option.
  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 9 Command Reference.
  4. When using a CREATE INDEX statement to create an index in a table that contains a large amount of data, a large amount of processing time may be required. Before executing the CREATE INDEX statement, set the timer monitoring specification as follows:
    • system-defined pd_watch_time
      Specify 0 or omit the specification.
    • Client environment definition PDCWAITTIME
      If the CREATE INDEX execution time can be estimated from past experience and the volume of data, specify the estimated value.
      If the execution time cannot be estimated, specify 0 or omit the specification.
  5. If the table storage RDAREA or specified index storage RDAREA is in a blocked state when defining an index in a row-partitioned table defined across multiple servers on a HiRDB/Parallel Server, the RDAREA will be in a lock wait state. In this case, an error may not be returned immediately when the specified value of the system-defined pd_lck_wait_timeout operand is reached and the process times out. To prevent this, cancel the blocked state of the table storage RDAREA or specified index storage RDAREA before executing the CREATE INDEX statement.