DROP INDEX statement

Use the DROP INDEX statement to remove an index.

This statement is an extension to the ANSI/ISO standard for SQL.

Syntax

DROP INDEX [IF EXISTS] [ owner . ] index [ONLINE]


Element	Description	Restrictions	Syntax
index	Name of the index to be dropped	Must exist in the database	Identifier
owner	Name of index owner	Must own the index	Owner name

Usage

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, distribution 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 quotation marks 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.

In some contexts, an alternative to the DROP INDEX statement is the SET Database Object Mode statement, which can disable a specified index without removing it from the system catalog. For more information about this SQL statement, which can also enable an index that is currently disabled, see SET Database Object Mode statement.

If you include the optional IF EXISTS keywords, the database server takes no action (rather than sending an exception to the application) if no index of the specified name is registered in the current database.

Example

An index such as the one found in the stores_demo database can be dropped with:

DROP INDEX zip_ix;

If necessary, you can specify the index name as the fully qualified four-part object name (database@instance:owner.indexname), as in the following:

DROP INDEX stores_demo@prod:"informix".zip_ix ;

Details of existing functions can be found in the sysprocedures system catalog table, as in the following:

SELECT idxname FROM sysindices ;