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.
When you drop an index, all extents of the index segment are returned to the containing tablespace and become available for other objects in the tablespace.
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. If you created the index explicitly with the
CREATE INDEX statement, then you can drop the index with the
DROP INDEX statement. The following statement drops the
DROP INDEX emp_ename;
Note:If a table is dropped, all associated indexes are dropped automatically.