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:
