To delete an index, use the DROP statement.
About this task
Other than changing the COMPRESSION attribute of an index,
you cannot change any clause of an index definition; you must drop
the index and create it again. Dropping an index does not cause any
other objects to be dropped but might cause some packages to be invalidated.
Restrictions
A
primary key or unique key index cannot be explicitly dropped. You
must use one of the following methods to drop it:
- If the primary index or unique constraint was created automatically
for the primary key or unique key, dropping the primary key or unique
key causes the index to be dropped. Dropping is done through the ALTER
TABLE statement.
- If the primary index or the unique constraint was user-defined,
the primary key or unique key must be dropped first, through the ALTER
TABLE statement. After the primary key or unique key is dropped, the
index is no longer considered the primary index or unique index, and
it can be explicitly dropped.
Procedure
To drop an index by using the command line, enter: DROP INDEX index_name
Results
Any packages and cached dynamic SQL and XQuery statements
that depend on the dropped indexes are marked invalid. The application
program is not affected by changes resulting from adding or dropping
indexes.