CREATE INDEX Format 1 (Define index)

Function

CREATE INDEX (format 1) defines an index for one or more columns of a base table.

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 usage privilege.

Format 1 (Define Index)

CREATE [UNIQUE] INDEX [authorization-identifier.]index-Identifier
    ON [authorization-identifier.]table-identifier(column-name [{ASC|DESC}]
                                    [, column-name [{ASC|DESC}]]...)
    [IN {RDAREA-name
         |(RDAREA-name|
         |((RDAREA-name)[, (RDAREA- name)]...)
         |matrix-partitioned-index-storage-RDAREA-specification}]
    [index-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 ::={PCTFREE =unused-space-percentage
             |UNBALANCED SPLIT
             |EMPTY
             |exception-value-specification}
exception-value-specification ::=EXCEPT VALUES (NULL[, NULL]...)

Operands

Specifies that the key values (all the values in the column or columns defined for the index) must be different (the value in each row must be unique).

If duplicate key values are detected during creation or updating of an index that has the UNIQUE attribute, HiRDB returns an error. However, a null value may be duplicated.

When the UNIQUE option is specified, the considerations discussed below should be noted.

1. When table row-partitioning is performed

The following table indicates the specifiability of UNIQUE in conjunction with row-partitioning.

Table 3-22 Specifiability of UNIQUE in conjunction with row-partitioning of a table

Table partitioning method#1Index constituent column for which UNIQUE is specified#2Index partitioning method#4UNIQUE specifi- ability
Row-partitioning within a serverKey range partitioning (not matrix partitioning) and FIX hash partitioningPartitioning key indexMatches the number of table partitions.Y
Does not match the number of table partitions.--
Non-partitioning key indexIncluding partitioning keys (in any order)Matches the number of table partitions.Y
Does not match the number of table partitions (no row-partitioning).Y
Not including partitioning keys (in any order)Matches the number of table partitions.N
Does not match the number of table partitions (no row-partitioning).Y#3
Flexible hash partitioningNot applicableNot applicableN
Matrix partitioningPartitioning key indexMatches the number of table partitions.Y
Does not match the number of table partitions.--
Non-partitioning key indexIncluding all partitioning keys (in any order)Matches the number of table partitions.Y
Does not match the number of table partitions.N
Not including any partitioning key (in any order)Matches the number of table partitions.N
Does not match the number of table partitions (no row-partitioning).--
Row-partitioning among servers (no partitioning within a server)Key range partitioning (not matrix partitioning) and FIX hash partitioningPartitioning key indexMatches the number of table partitions.Y
Does not match the number of table partitions.--
Non-partitioning key indexIncluding all partitioning keys (in any order)Matches the number of table partitions.Y
Does not match the number of table partitions.--
Not including any partitioning key (in any order)Matches the number of table partitions.N
Does not match the number of table partitions.--
Flexible hash partitioningNot applicableNot applicableN
Matrix partitioningPartitioning key indexMatches the number of table partitions.Y
Does not match the number of table partitions.--
Non-partitioning key indexIncluding all partitioning keys (in any order)Matches the number of table partitions.Y
Does not match the number of table partitions.--
Not including any partitioning key (in any order)Matches the number of table partitions.N
Does not match the number of table partitions.--
Row-partitioning among servers (with partitioning within a server)Key range partitioning (not matrix partitioning) and FIX hash partitioningPartitioning key indexMatches the number of table partitions.Y
Does not match the number of table partitions.--
Non-partitioning key indexIncluding all partitioning keys (in any order)Matches the number of table partitions (also partitioned on the server)Y
Does not match the number of table partitions (not partitioned on the server)Y
Not including any partitioning key (in any order)Matches the number of table partitions (also partitioned on the server)N
Does not match the number of table partitions (not partitioned on the server)N
Flexible hash partitioningNot applicableNot applicableN
Matrix partitioningPartitioning key indexMatches the number of table partitions.Y
Does not match the number of table partitions.--
Non-partitioning key indexIncluding all partitioning keys (in any order)Matches the number of table partitions.Y
Does not match the number of table partitions.--
Not including any partitioning key (in any order)Matches the number of table partitions.N
Does not match the number of table partitions.--
Legend:
Y: UNIQUE can be specified.
N: UNIQUE cannot be specified.
--: An index cannot be defined.
#1: Row-partitioning within a server refers to either row-partitioning on a HiRDB/Single Server or row-partitioning that is closed on a back-end server on a HiRDB/Parallel Server. Row-partitioning among servers refers to row-partitioning that extends across multiple servers on a HiRDB/Parallel Server. If a server contains a mix of tables, some whose table partitioning method is row-partitioning within a server and some whose table partitioning method is row-partitioning among servers, that server is classified as row-partitioning among servers. For details about row-partitioning of tables, see the HiRDB Version 9 Installation and Design Guide.
#2: Partitioning key index refers to an index in which the column (partitioning key) in which a storage condition is specified for the row-partitioning of a table is the first constituent column, and such indexes include the following:
Single-column partitioning:
A single-column index that is created in a partitioning key, or a multiple-column index that is created in multiple columns for which the partitioning key is the first column
Multiple-column partitioning:
A multiple-column index that is created in multiple columns and that contain all of the columns specified for partitioning from the beginning in the same order
Indexes that are not partitioning key indexes are referred to as non-partitioning key indexes.
#3: The N mark indicates the use of the rebalancing facility for hash-partitioned tables (due to the fact that UNIQUE cannot be specified if an RDAREA is added to change the partitioning to row-partitioning among servers).
#4: The method by which an index is partitioned depends on how the index storage RDAREA is specified. With the exception of flexible hash partitioning. omitting the IN operand may prevent specification of UNIQUE, due to the index partitioning method. Notice that explicitly specifying an index storage RDAREA in the IN operand may prevent the specification of UNIQUE.
2. When a repetition column is used

A repetition column cannot be specified as a constituent column of an index for which UNIQUE is specified.

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

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

Specifies the authorization identifier of the user who owns the table that is to be indexed and the name of the base table for which the index is being created.

column-name
Specifies the name of a column for which the index is being defined.
A maximum of 16 column names can be specified.
When multiple column names are specified, each column name must be unique.
ASC
Specifies that the index is to be organized in ascending order of the key values.
DESC
Specifies that the index is to be organized in descending order of the key values.

Specifies the names of the RDAREAs in which the index is to be stored.

Specify matrix-partitioned-index-storage-RDAREA-specification when defining an index for a matrix-partitioned table. RDAREA names are subject to the same rules as non-matrix-partitioned tables.

The following rules apply to RDAREAs that store indexes:

  1. In addition, the following restrictions apply to HiRDB/Parallel Servers: If the table specified in table-identifier is a shared table, the RDAREA name must be a shared RDAREA. If the table specified in table-identifier is not a shared table, a shared RDAREA cannot be specified in the RDAREA name.
  2. The specified RDAREAs must have already been created by the database initialization utility or added by the database structure modification utility.
  3. If no RDAREA names are specified, the index is stored in the RDAREAs in which the table specified by the table identifier is stored. However, if the table is partitioned in a HiRDB/Single Server or is partitioned in the same back-end server in a HiRDB/Parallel Server, an index in which a column other than the partitioning key is specified at the beginning of the index is stored in the first table-storage RDAREA for which a partitioning condition was specified. For a matrix-partitioned table, a two-dimensional RDAREA specification cannot be omitted unless all the columns specified in a partitioned key are specified in the same order from the beginning of index constituent columns.
  4. The same RDAREA name cannot be used when specifying multiple RDAREAs. However, duplicate storage RDAREA names can be specified in row-partitioned tables, hash-partitioned tables, and matrix-partitioned tables that specify a boundary value.
  5. If a table is partitioned and stored in multiple RDAREAs, the index storage RDAREA is specified as follows:
    • For a partitioning key index, specify the same number of RDAREA names as the number of RDAREAs containing the table. In this case, the index will be stored in the order in which the 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.
    Definition examples are as follows:
    Example 1: Partitioning by rows based on storage conditions:
    [Figure]
    Example 2: Partitioning by rows based on a boundary value:
    [Figure]
    Example 3: Matrix partitioning

    [Figure]

  6. For a HiRDB/Parallel Server, the RDAREAs that store the table and the RDAREAs that store the associated index must be located in the same back-end server.
  7. For a non-partitioning key index, specify RDAREAs as follows:
    • If an index is not row-partitioned within a server, specify RDAREA names in a number equal to the servers on which partitioned tables are stored. For a HiRDB/Parallel Server, specify an RDAREA for each back-end server containing a table. For a HiRDB/Single Server, only one RDAREA can be specified. Matrix-partitioned tables are treated in the same way as a partitioned key index.
    • If an index is to be row-partitioned within a server, specify RDAREA names in a number equal to the RDAREAs storing the table. In this case, the targets of index storage correspond to the order in which table storage RDAREAs were specified in CREATE TABLE. Also, 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.
    The RDAREAs can be specified in any order; the indexes associated with the table storage RDAREAs at the same server will be stored respectively.
  8. User RDAREAs storing rebalancing tables cannot be specified.
  9. When an index for a rebalancing table is defined, an RDAREA name must be specified.

Specifies the percentage of unused space to be left in each index page when the index is created. The range of specifiable values is 0 to 99, and the default is 30.

When created in batch by the database load utility and the database reorganization utility, indexes are created in a percentage equal to the percentage of unused space. In other addition or update operations by INSERT or UPDATE statements, the default PCTFREE=0 is assumed.

If rows will be added frequently after the index has been created, a high percentage of unused space should be specified.

Specifies that the key values are to be allocated unevenly among the pages when a page is split.

If the location where a key value is to be inserted is in the first half of the page to be split, more empty space is allocated to the left-side page after the split. If the key value insertion location is in the second half of the page, more empty space is allocated to the right-side page after the split. This is called an unbalanced index split.

For details of unbalanced index splits, see the HiRDB Version 9 System Operation Guide.

Specifies that an unfinished index is to be created.

The EMPTY option improves the capacity for concurrent execution of 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. The 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 that when the index is created, key values that are composed solely of null values are to be excluded.

The exception values option cannot be specified for an index that contains NOT NULL constraint columns. When the exception values option is specified for an index, indexes cannot be unloaded in index order by the database reorganization utility.

Constituent columns of an index for which the exception values option is specified cannot include repetition columns.

Common rules

  1. A maximum of 255 indexes can be created for a table.
  2. An index can be defined for columns that contain the null value and for columns that do not have any rows.
  3. An index cannot be defined for columns of the following data types:
    • BLOB
    • BINARY
    • Abstract data type
  4. If an index is composed of multiple columns, columns of the following data types, in addition to item 3, cannot be specified:
    • FLOAT
    • SMALLFLT
  5. The total length of columns comprising an index must satisfy the following formula:
    Total length of columns [Figure]
    MIN((page size of index storage RDAREAs[Figure] 2) - 1242, 4036)
    The following table lists the lengths of the columns that comprise an index.

    Table 3-23 Lengths of multicolumn index columns

    Data typeWhen the combined length of the columns does not exceed 255 bytesWhen the combined length of the columns exceeds 255 bytes
    Columns comprising a single column indexColumns comprising a multicolumn indexColumns comprising a single column indexColumns comprising a multicolumn index
    Fixed length columns onlyVariable length columns are also includedFixed length columns onlyVariable length columns are also included
    INTEGER456--57
    SMALLINT234--35
    DECIMAL[(m[,n])][Figure]m[Figure] 2[Figure] + 1[Figure]m[Figure] 2[Figure] + 2[Figure]m[Figure] 2[Figure] + 3--[Figure]m[Figure] 2[Figure] + 2[Figure]m[Figure] 2[Figure] + 4
    FLOAT8----------
    SMALLFLT4----------
    CHAR(n), MCHAR(n)n1n1 + 1n1 + 2n1n1 + 1n1 + 3
    NCHAR(n)2 x n22 x n2 + 12 x n2 + 22 x n22 x n2 + 12 x n2 + 3
    DATE456--57
    TIME345--46
    TIMESTAMP7 + p[Figure] 28 + p[Figure] 29 + p[Figure] 2--8 + p[Figure] 210 + p[Figure] 2
    INTERVAL YEAR TO DAY567--68
    INTERVAL HOUR TO SECOND456--57
    VARCHAR, MVARCHARn1 + 1--n1 + 2n1 + 2--n1 + 3
    NVARCHAR2 x n2 + 1--2 x n2 + 22 x n2 + 2--2 x n2 + 3
    m, n: Positive integers
    n1: Actual data length
    n2: Number of characters
    p: Fractional second precision
    --: Not applicable
  6. Only one index can be defined for a single column, regardless of whether it is sorted in ascending order or descending order. For a multicolumn index, two indexes are considered to be identical if all their member columns sorted in ascending order are exact inverses of their descending-order counterparts.
  7. If a procedure and a trigger are already defined for the table for which an index is to be defined, index information in the SQL object is nullified, and the trigger cannot be executed. Because the affected procedure or the trigger cannot be executed from another procedure, the SQL object needs to be re-created.
  8. The same index option cannot be specified more than once.
  9. If an index composed of multiple repetition columns is defined, the repetition columns must have the same number of current elements.
  10. ALTER TABLE cannot be executed from a Java procedure if the execution result invalidates the SQL object being executed.
  11. 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.
  12. For execution conditions for CREATE INDEX using the inner replica facility, see the manual HiRDB Staticizer Option Version 9.
  13. A maximum of 500 indexes can be stored per RDAREA.

Notes

  1. When a value in an indexed column is updated, the associated index is also updated.
  2. When a multicolumn index is defined, the order in which the columns are specified is the order in which the key values are created.
  3. A multicolumn index can include a column for which a single-column index is defined.
  4. The CREATE INDEX statement cannot be specified from an X/Open-compliant UAP running under OLTP.
  5. When an index is defined with the EMPTY option specified, it must be re-created with the index re-creation function of the database reorganization utility; for details, see the manual HiRDB Version 9 Command Reference.
  6. For rules on defining an index for a table with a WITHOUT ROLLBACK specification, see the rules on WITHOUT ROLLBACK in CREATE TABLE (Define table).
  7. An index for which the exception values option is specified cannot be used in an SQL statement that is subject to selection of exception value rows.
  8. 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.
  9. 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.

Examples

  1. Define an ascending-order index (IDX1) for the product code (PCODE) column of a stock table (STOCK); assume that rows will be added frequently after the index has been defined (specify 50 as the percentage of unused space to be left in the index pages):

    CREATE INDEX IDX1
      ON STOCK(PCODE ASC)
      PCTFREE = 50

  2. Define a multicolumn index (IDX2) for the product name (PNAME) and color (COLOR) columns of a stock table (STOCK), and store the index in a user RDAREA (RDA1):

    CREATE INDEX IDX2
      ON STOCK(PNAME,COLOR)
      IN RDA1

  3. Define an ascending-order index (IDX3) for the product code (PCODE) column of a stock table (STOCK); partition the index and store it in RDAREAs RDA1, RDA2, and RDA3; assume that the stock table is stored on a split basis in three RDAREAs, using the product code as the partitioning key:

    CREATE INDEX IDX3
      ON STOCK(PCODE)
      IN ((RDA1),(RDA2),(RDA3))