Thursday, 20 October 2016

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_CONSTRAINTS performance 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:



3 comments:

  1. You might want to read here:

    http://dwhlaureate.blogspot.in/2016/10/how-to-drop-indexes-unique-indexes-in.html

    before you continue to post that if an index sin't appearing in an execution plan it isn't used. That isn't always the case.

    ReplyDelete
  2. You do NOT need to drop an index to rebuild it!
    THis is not SAP Hanna nor MSSQL!

    ReplyDelete

Related Posts Plugin for WordPress, Blogger...

ShareThis