DROP INDEX statement

Removes an index from the database.

Syntax

DROP INDEX [ IF EXISTS ] { [ [ owner.]table-name.]index-name | [ [ owner.]materialized-view-name. ]index-name }

Remarks

Use the IF EXISTS clause if you do not want an error returned when the DROP INDEX statement attempts to remove an index that does not exist.

When you specify the IF EXISTS clause and the named table cannot be located, an error is returned.

DROP INDEX is prevented when the statement affects an object that is currently being used by another connection.

The DROP INDEX statement cannot be executed when there are cursors opened with the WITH HOLD clause that use either statement or transaction snapshots.

Privileges

To drop an index on a table, you must be the owner of the table, or have one of the following privileges:

REFERENCES privilege on the table
DROP ANY INDEX system privilege
DROP ANY OBJECT system privilege

To drop an index on a materialized view, you must be the owner of the materialized view, or have one of the following privileges:

DROP ANY INDEX system privilege
DROP ANY OBJECT system privilege

Side effects

Automatic commit. The DROP INDEX statement closes all cursors for the current connection.

If you use the DROP INDEX statement to drop an index on a local temporary table an error is returned indicating that the index could not be found. Use the DROP TABLE statement to drop a local temporary table. Indexes on local temporary tables are dropped automatically when the local temporary table goes out of scope.

Standards

ANSI/ISO SQL Standard
Not in the standard.

Example

This example drops a fictitious index, MyIndex, from the database.

DROP INDEX MyIndex;