Altering an Index

To change the definition of an index, follow these steps:

Use ALTER INDEX to make the current index inactive.
Drop the current index.
Create a new index and give it the same name as the dropped index.

An index is usually modified to change the combination of columns that are indexed, to prevent or allow insertion of duplicate entries, or to specify index sort order. For example, given the following definition of the NAMEX index:

EXEC SQL
CREATE INDEX NAMEX ON EMPLOYEE (LAST_NAME, FIRST_NAME);

Suppose there is an additional need to prevent duplicate entries with the UNIQUE keyword. First, make the current index inactive, then drop it:

EXEC SQL
ALTER INDEX NAMEX INACTIVE;
EXEC SQL
DROP INDEX NAMEX;
EXEC SQL
COMMIT;

Then create a new index, NAMEX, based on the previous definition, that also includes the UNIQUE keyword:

EXEC SQL
CREATE UNIQUE INDEX NAMEX ON EMPLOYEE (LAST_NAME, FIRST_NAME);
EXEC SQL
COMMIT

ALTER INDEX can be used directly to change an index’s sort order, or to add the ability to handle unique or duplicate entries. For example, the following statement changes the NAMEX index to permit duplicate entries:

EXEC SQL
ALTER INDEX NAMEX DUPLICATE;

Important:
Be careful when altering an index directly. For example, changing an index from supporting duplicate entries to one that requires unique entries without disabling the index and recreating it can reduce index performance.

For more information about dropping an index, see Dropping an Index. For more information about creating an index, see Creating an Index.

Advance To:

Embedded SQL Guide