Index

You can create an index (also called a secondary key) to make it quicker to find data records in a table. An index is a database object that can be defined for an individual column or a series of columns in a database table. An index consists of one or more inversion lists.

An index name must be unique when combined with the table name. When defining an index, you determine whether the column values of different rows in the indexed columns have to be unique or not.

Since indexes are derived entirely from data already in the database, you can delete them without data being lost.

Note that, in certain circumstances, the database system may have to update the index corresponding to any data changes, which takes time.

An index can have the following statuses:

Status

Description

OK

The index is intact.

BAD

The index is damaged.

The index’s B* tree is damaged and the database system can no longer use it. The database system ignores damaged indexes. These cannot cause errors, but do impair performance.

MISSING

The index is available in the database catalog, but is missing in the file directory.

You can recreate damaged indexes using the Database Manager. If you set the AUTO_RECREATE_BAD_INDEXES Special Database Parameter to YES, then the database system automatically recreates damaged indexes.

You can use the EXPLAIN statement to check whether an index is really used when the database system searches for data records.

See also:

SQL Reference Manual, CREATE INDEX Statement (create_index_statement), EXPLAIN Statement (explain_statement)

Database Manager GUI, Restoring Indexes After Database Restore

Database Manager CLI, sql_recreateindex

Concepts of the Database System, B* Trees for Tables with Indexes

Database Administration in CCMS, Indexes