DROP INDEX statement 

The DROP INDEX statement (drop_index_statement) drops an index of a base table. The metadata of the index is dropped from the database catalog. The storage space occupied by the index is released.

Syntax

<drop_index_statement> ::= DROP INDEX <index_name> [ON <table_name>]

Examples

SQL Tutorial, Indexes

Explanation

If a schema name is not specified in the table name, the current schema is assumed implicitly.

The specified index must exist and the specified table name must be the name of an existing base table.

ON <table_name>is not necessary if the index name identifies an index unambiguously.

The current user must be the owner of the specified table or have the INDEX privilege for it.