Background documentationALTER INDEX Statement (alter_index_statement) Locate this document in the navigation structure

 

The ALTER INDEX statement (alter_index_statement) controls how an index is used for data queries.

Structure

Syntax Syntax

  1. <alter_index_statement> ::=
      ALTER INDEX <index_name> [ON <table_name>] ENABLE
    | ALTER INDEX <index_name> [ON <table_name>] DISABLE
    | ALTER INDEX <index_name> [ON <table_name>] INIT USAGE
End of the code.
Explanation

If a schema is not specified in the table name, the current schema is accepted implicitly. The current database user must have the INDEX privilege for the columns.

When a CREATE INDEX statement is executed, an index is generated across the specified columns. This index is modified accordingly for all of the following SQL statements for data manipulation (INSERT statement, UPDATE statement, DELETE statement). With all other SQL statements in which individual rows in a table are specified, the database system can use this index to speed up the search for these rows.

ALTER INDEX ... DISABLE

The index can no longer be used for this search, however it is still changed when the SQL statements INSERT, UPDATE or DELETE are used.

ALTER INDEX ... ENABLE

The index can be used again for the search.

ALTER INDEX ... INIT USAGE

The INDEX_USED column in the system table DOMAIN.INDEXES is initialized with 0. This means that the mechanism that counts how frequently an index is used is restarted.

More Information

Privileges: Overview