You can create an index (also called secondary key) to make it quicker for the database system to find data records in a table. An index is a database object that can be created 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.
Property |
Description (Link to SQL Reference Manual) |
---|---|
Name |
|
Unique |
Specifies whether the values for this columns must be unique |
Disabled |
Specifies whether the index is enabled or disabled |
Indexed Columns |
Columns that are indexed |
Usage Count |
Shows how often the system has used the index |
Bad |
Shows whether the index is damaged |
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.
State |
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 internal file directory. |
You can automate recreating of damaged indexes using Database Studio, Automatic Recreation of Bad Indexes ( Activating or Deactivating the Automatic Recreation of Damaged Indexes).
You can use the EXPLAIN statement to check whether an index is really used when the database system searches for data records.
Concepts of the Database System, B* Trees for Tables with Indexes
Database Studio,
SQL Tutorial, Indexes
SQL Reference Manual, Index Name (index_name), EXPLAIN Statement
Database Manager CLI, sql_recreateindex, auto_recreate_bad_index
Database Administration in CCMS: Indexes
SAP Note 928037 (FAQ: SAP MaxDB Indexes)