How to Drop Indexes/ Unique Indexes in Oracle?

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 table

space once you drop the index so that it becomes available to other objects in the table space.

Below is the command to drop indexes:

SYNTAX : DROP INDEX [OWNER.]INDEXNAME [FROM [OWNER.]TABLENAME]

EXAMPLE:

SQL> DROP INDEX EMP_NAME_IDX;

INDEX DROPPED

 SQL>

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.

SQL> DROP INDEX EMP_NAME_IDX ;

 DROP INDEX EMP_NAME_IDX *

ERROR AT LINE 1: ORA-02429: CANNOT DROP INDEX USED FOR ENFORCEMENT OF UNIQUE/PRIMARY KEY

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:

SQL> ALTER TABLE EMP DROP CONSTRAINT emp_name_PK1;

TABLE ALTERED.

SQL>

You can query the ALL_CONSTRAINTSperformance view to understand which constraint the index is used by,

SELECT OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE,

 TABLE_NAME, INDEX_OWNER, INDEX_NAME

FROM ALL_CONSTRAINTS

WHERE INDEX_NAME = 'EMP_NAME_IDX';

If you like this post, please share it on google by clicking on the Google +1 button.

Please go through similar Oracle Posts @DWHLAUREATE:

     WHAT IS A PARTITION IN ORACLE

   WHAT IS AN INLINE VIEW?

  WHAT ARE EXTERNAL TABLES IN ORACLE

 WHAT ARE INDEXES IN ORACLE 

Tweet