Dropping Indexes

You might want to drop an index for any of the following reasons:

• The index is no longer required.

• The index is not providing anticipated performance improvements for queries issued against the associated table. (For example, the table might be very small, or there might be many rows in the table but very few index entries.)

• Applications do not use the index to query the data.

• The index has become invalid and must be dropped before being rebuilt.

• The index has become too fragmented and must be dropped before being rebuilt.

How you drop an index depends on whether you created the index explicitly with a CREATE INDEX statement, or implicitly by defining a key constraint on a table.

Note: If a table is dropped, all associated indexes are dropped automatically.

You cannot drop only the index associated with an enabled UNIQUE key or PRIMARY KEY constraint. To drop a constraint's associated index, you must disable or drop the constraint itself.

You can drop an explicitly created index with the SQL command DROP INDEX. For example, to drop the EMP_ENAME index, you would enter the following statement:

DROP INDEX emp_ename;

See Also: For information about analyzing indexes, see "Analyzing Tables, Indexes, and Clusters" .

For more information about dropping a constraint's associated index, see "Managing Integrity Constraints" .