CREATE INDEX Statement

Create a new index on the specified columns in an SQL table.

Syntax

CREATE {UNIQUE} INDEX index-name ON base-table-name (column-identifier {[ASC || DESC]}{, column-identifier}…)

Syntax Elements

index-name =
{index-qualifier.}index-identifier

index-qualifier
This must be the same as base-table-name.

index-identifier
Any user-defined name (except a Reserved Word) starting with a letter and comprising up to 49 letters, underscores and/or numbers. Letters are case sensitive.

base-table-name =
{owner-name.}base-table-identifier

owner-name
The SQL user-id of the creator of the table.

base-table-identifier
The name of the table to index.

column-identifier
The name of the column to be indexed.

Restrictions

• Only the SQL owner of the table can perform this statement. The owner must have SQL Catalog Maintenance privilege set in the Reality security profile.

• A column can only be indexed if, in its column definition fields:

  • Its attribute number (field 5) is a number. Exploding attribute columns cannot be indexed (attribute number = “E”), neither can “special” columns (attribute number = “NA”, “NV” or “NS”).
  • Its value number (field 6) is “1”, “” (equivalent to “1”) or “E”.

  • Its subvalue number (field 7) is “1”, “” (equivalent to “1”) or “E”.

    Notes:

    • If the value number is “1”, the subvalue number must be “1”.
    • If value number is “E”, the subvalue number may be “1” or “E”

  Refer to SQL Column and Index Definitions for details of the SQL column definition fields.

Comments

See Recommendations for Creating Indexes.

This statement creates a regular Reality index that can also be used by other Reality processes such as English.

An index created with the CREATE INDEX statement will be deleted by a DROP TABLE statement on that table.

If the UNIQUE option is used, the indexed column(s) will be constrained to contain no more than one occurrence of each value (including null).

The index can be created in ascending (ASC) or descending (DESC) ASCII order. Ascending order is the default, if neither ASC nor DESC is specified.

Where exploded multi- or sub-values exist, one index entry is created per multi- or sub-value. (Exploding multi- or sub-values places them in separate table rows. They are specified as exploding via fields 6 or 7 of the SQL column definition. For more information about column definition, see SQL Column and Index Definitions.

Examples

CREATE INDEX DEPTIDX ON EMP(DEPTNO)

CREATE UNIQUE INDEX RB2 ON EMP(DEPTNO ASC, EMPNO DESC)