DROP INDEX
Function
DROP INDEX deletes an index.
Precautions
Only the index owner or a user who has the INDEX permission on the table where the index resides can run the DROP INDEX command. The system administrator has this permission by default.
For a global temporary table, if a session has initialized a global temporary table object (including creating a global temporary table and inserting data into the global temporary table for the first time), other sessions cannot delete indexes from the table.
Syntax
DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ]
index_name [, ...] [ CASCADE | RESTRICT ];
Parameter Description
CONCURRENTLY
Deletes an index without locking it. A normal DROP INDEX acquires exclusive lock on the table on which the index depends, blocking other accesses until the index drop can be completed. With this option, the statement does not lock the table during index deletion.
This parameter allows only one index name and does not support CASCADE.
The DROP INDEX statement can be run within a transaction, but DROP INDEX CONCURRENTLY cannot.
IF EXISTS
Reports a notice instead of an error if the specified index does not exist.
index_name
Specifies the index name to be deleted.
Value range: an existing index
CASCADE | RESTRICT
- CASCADE: automatically deletes the objects that depend on the index.
- RESTRICT: refuses to delete the index if any objects depend on it. This is the default action.
Examples
See Examples in CREATE INDEX.