There can be multiple situations where we don’t require indexes and have to drop them.
- Sometimes it’s better to drop the indexes when there is not much performance gain for your table with indexes.
- Once the indexes becomes invalid, you must first drop the indexes before rebuilding it.
- If your indexes are too fragmented, it’s better to drop the indexes and create a new index since rebuilding an index requires twice the space of the index.
All the extents of the index segment are restored to the containing tablespace once you drop the index so that it becomes available to other objects in the table space.
Below is the command to drop indexes:
Conversely, you can't drop any implicitly created index, such as those created by defining a UNIQUE key constraint on a table, with the drop index command. If you try to do so it will throw an error.
If you want to drop such an index you have to first drop the constraint defined on the table. In order to drop a constraint, issue the drop constraint command, as shown here:
You can query the ALL_CONSTRAINTS performance view to understand which constraint the index is used by,