Use the DROP INDEX statement to drop a table index. An index can only be dropped from tables with more than one index. The initial index of a table cannot be dropped.

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.

Examples: DROP INDEX statement

The following example demonstrates the use of a DROP INDEX statement.

DROP INDEX idx_cust ON customer;

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

DROP INDEX custnum ON Pub.tpcustomer;

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;