CREATE INDEX

The CREATE INDEX data description statement defines an index on a base table. The index definition is stored in the dictionary. It is also a CA IDMS extension of the SQL standard.

Authorization

To issue a CREATE INDEX statement, you must:

Hold the ALTER privilege on or own the table on which the index is being defined
Hold the USE privilege on the area where the named index is stored

Syntax

►►─── CREATE ─┬──────────┬─ INDEX index-name ─────────────────────────────────►
              └─ UNIQUE ─┘

 ►─── ON ─┬────────────────┬─ table-identifier ───────────────────────────────►
          └─ schema-name. ─┘

 ►─┬───────────────────────────────────────┬──────────────────────────────────►
   │     ┌──────────── , ────────────┐     │
   └─ ( ─▼─ column-name ─┬─────────┬─┴─ ) ─┘
                         ├─ ASC ◄──┤
                         └─ DESC ──┘

 ►─┬──────────────────┬───────────────────────────────────────────────────────►
   ├─ COMPRESSED ─────┤
   └─ UNCOMPRESSED ◄──┘

 ►─┬─────────────────────────────┬─┬─────────────────────────────┬────────────►
   └─ IN segment-name.area-name ─┘ └─ index-block-specification ─┘

 ►─┬─────────────┬────────────────────────────────────────────────────────────►
   └─ CLUSTERED ─┘

 ►─┬────────────────────────────┬─────────────────────────────────────────────►◄
   └─ INDEX ID index-id-number ─┘

Expansion of index-block-specification

►►─── INDEX BLOCK CONTAINS key-count KEYs ────────────────────────────────────►

 ►─┬─────────────────────────────────────┬────────────────────────────────────►◄
   └─ DISPLACEMENT IS page-count PAGES ──┘

Parameters

UNIQUE

Specifies that the index-key value in any given row of the table on which the index is being defined must be different from the index-key value in any other row of the table. A table with a unique index cannot contain duplicate index key values.

If you specify UNIQUE, and the table on which the index is being defined contains duplicate rows, CA IDMS returns an error.

index-name

Specifies the name of the index being created. Index-name must be a 1- through 18-character name that follows the conventions for SQL identifiers.

Index-name must be unique for the table on which the index is defined.

ON table-identifier

Specifies the table on which the index is being defined. Table-identifier must identify a base table defined in the dictionary.

If you specify CLUSTERED in a CREATE INDEX statement, the named table:

Cannot have a CALC key defined on it
Cannot have another clustered index defined on it
Cannot be the referencing table in a clustered referential constraint

schema-name

Identifies the schema associated with the named table.

If you do not specify schema-name, it defaults to:

The current schema associated with your SQL session, if the statement is entered through the Command Facility or executed dynamically
The schema associated with the access module used at runtime, if the statement is embedded in an application program

(column-name)

Specifies one or more columns that make up the index key. CA IDMS maintains index entries in ascending or descending order according to the values in the specified columns. Entries are ordered first by the first column specified, then by the second column specified within the ordering established by the first column, then by the third column specified, and so on.

Column-name must identify a column in the table on which the index is being created and must be unique within the list of column names.

You can include from 1 through 32 columns in an index key.

If no column name is specified, CA IDMS creates an index on the db-key sorted in ascending order.

ASC

Indicates that values in the named column are to be sorted in ascending order. ASC is the default when you specify neither ASC nor DESC.

DESC

Indicates that values in the named column are to be sorted in descending order.

COMPRESSED

Directs CA IDMS to maintain index entries in a compressed form in the database.

UNCOMPRESSED

Directs CA IDMS to maintain index entries in an uncompressed form in the database.

UNCOMPRESSED is the default when you specify neither COMPRESSED nor UNCOMPRESSED.

IN

Specifies the area to be used to store entries in the index.

If you do not associate an area with an index, CA IDMS uses the area associated with the table on which the index is being defined.

segment-name

Identifies the segment associated with the area.

area-name

Identifies the area to be associated with the index. Area-name must identify an area defined in the dictionary.

index-block-specification

Establishes characteristics of the index.

Syntax for index-block-specification immediately follows the syntax for CREATE INDEX.

If index-block-specification is omitted, values for key-count and page-count are calculated by CA IDMS using available information about actual or estimated row count for the table on which the index is being defined.

CLUSTERED

Specifies that each row of the table on which the index is being defined is to be stored as close as possible to the table row with the immediately preceding index-key value.

INDEX ID index-id-number

Assigns an index ID value for the index being created. The index-id-number must be in the range of 1 through 32767.

Parameters for Expansion of index-block-specification

key-count KEYs

Establishes the maximum number of entries in each internal index record (SR8 system record).

Key-count must be an unsigned integer in the range 3 through 8180.

page-count PAGES

Indicates how far away from the top of the index (the SR7 system record) the bottom-level index records are to be stored.

Page-count must be an unsigned integer in the range 0 through 32767.

If the value of key-count is 0, the bottom-level internal index records are not displaced from the SR7 record.

Usage

Specifying an Index ID

When defining an index you can specify a value for its numeric index identifier. If explicitly specified, it must be unique across all other indexes residing in the same database area. If not specified, the index's numeric identifier is automatically set to the next available number in the range 1 through 32,767.

SYSTEM Tables

You cannot define an index on a table in the SYSTEM schema.

SYSTEM Areas

You cannot associate an index with a system area supplied with CA IDMS.

Order of Null Values

If the value of an index key column is null, it is treated as higher than all non-null values.

Null Values in Unique Indexes

Nullable columns are allowed to be used in a UNIQUE index. Null values are treated like any other value when the uniqueness of an index is evaluated. For example, a single column index can only contain one null value.

Mixed Page Group

An index must reside in the same page group as the table on which the index is created.

Example

Defining a Unique Index

The following CREATE INDEX statement defines a unique index on the JOB table. The index key consists of two columns: JOB_ID and JOB_TITLE. The index entries are stored in compressed form in the same area as the JOB table.

create unique index job_title_index
   on job
      (job_id, job_title)
   compressed;

Defining a Clustered Index

The following CREATE INDEX statement defines an index on the MONTHLY_BUDGET table. The index key consists of two columns: FISCAL_YEAR and MONTH. The index entries are stored in compressed form in the SALESSEG.SALES_X_AREA area. Rows of the MONTHLY_BUDGET table that have consecutive index-key values are stored close to each other.

create index budget_date_index
   on sales_sch.monthly_budget
      (fiscal_year desc, month)
   compressed
   in salesseg.sales_x_area
   clustered;

More Information

For more information about dropping indexes, see DROP INDEX.
For more information about implementing indexes, see the CA IDMS Database Design Guide.
For more information about calculations, see Error! Reference source not found..

More information:

DROP INDEX