Expert Indexing in Oracle Database 11g: Maximum Performance for Your Database

Dropping an Index

On occasion, you may find that you don't need an index because the index isn't providing any real performance gains or because your application doesn't use the index. You can drop an index by using the drop index command. You may also drop an index because the index is invalid and you want to rebuild it. You must first drop the index before rebuilding it. You'll also find that if an index is too fragmented, it is better to drop it and create a brand new index instead of rebuilding it; as you recall, rebuilding an index requires twice the space of the index.

You execute the drop index command in the following manner:

SQL> drop index test_idx SQL> / Index dropped. SQL>

You can drop any index that you have explicitly created ...