ALTER INDEX

Description

The ALTER INDEX statement rebuilds an index. In other words, it drops and rebuilds an index. If a table name and a column name are added at the end of the ON clause, a new index is re-created with the table and column names.

Syntax

ALTER [ REVERSE ] [ UNIQUE ] INDEX [index_name]
[ON { ONLY } table_name ( column_name [ {, column_name } ...) ] REBUILD [ ; ]

• REVERSE: Creates an index in the reverse order. A reverse index helps to increase sorting speed in descending order.
• UNIQUE: Creates an index with unique values.
• index_name: Specifies the name of the index to be recreated. The index name must be unique in the table. If omitted, a name is automatically assigned as i_<table_name>_<column_names>.
• table_name: Specifies the name of the table where the index is to be recreated.
• column_name: Specifies the name of the column where the index is to be applied. To create a multiple column index, specify two or more column names.

Example

The following is an example of re-creating indexes in various ways:

ALTER INDEX i_game_medal ON game(medal) REBUILD;

ALTER INDEX game_date_idx REBUILD;