>>-DROP INDEX--+------------+--index--+-------------+----------><
               '- owner-- .-'         |        (1)  |
                                      '-ONLINE------'
 

1. Dynamic Server only

In a typical online transaction processing (OLTP) environment, concurrent applications are connected to the database server to perform DML operations. For every query, the optimizer chooses a plan that is based on existing indexes, statistics, and directives. After numerous OLTP transactions, however, the chosen plan might no longer be the best plan for query execution. In this case, dropping an inefficient index can sometimes improve performance.

You must be the owner of the index or have the DBA privilege to use the DROP INDEX statement. The following example drops the index o_num_ix that joed owns. The stores_demo database must be the current database:

DROP INDEX stores_demo:joed.o_num_ix; 

You cannot use the DROP INDEX statement to drop a unique constraint, nor to drop an index that supports a constraint; you must use the ALTER TABLE ... DROP CONSTRAINT statement to drop the constraint. When you drop the constraint, the database server automatically drops any index that exists solely to support that constraint. If you attempt to use DROP INDEX to drop an index that is shared by a unique constraint, the database server renames the specified index in the sysindexes system catalog table, declaring a new name in this format:

[space]<tabid>_<constraint_id>

Here tabid and constraint_id are from the systables and sysconstraints system catalog tables, respectively. The sysconstraints.idxname column is then updated to something like: " 121_13" (where quotes show the leading blank space). If this index is a unique index with only referential constraints sharing it, the index is downgraded to a duplicate index after it is renamed.