Deletes an index on the specified table.

If a multi-tenant index is dropped, then the DROP INDEX statement deletes the index for every tenant defined in the table.

You can also use the DROP INDEX statement to drop an index on a partitioned table and delete all the index partitions corresponding to the index being dropped. However, the index created for a constraint on the table cannot be dropped.

Syntax

DROP INDEX [index_owner_name]index_name 
    ON [table_owner_name]table_name  ;

Parameters

index_owner_name

Specifies the name of the index owner. If index_owner_name is specified and is different from the name of the user executing the statement, then the user must have DBA privileges.

index_name

Verifies the index_name corresponding to the table.

Examples

DROP INDEX statement

The following example illustrates the DROP INDEX statement:

DROP INDEX custindex ON customer;

You cannot drop the first index created on a table, except by dropping the table.

Dropping a local index

The following example drops the local index custnum including all its partitions on the table Pub.tpcustomer:

DROP INDEX custnum ON Pub.tpcustomer;

Dropping a global index

The following example drops the global index custname, which has only one physical partition and that physical partition is dropped:

DROP INDEX custname ON Pub.tpcustomer;