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:



12 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
  3. It's really good for knowledge building.

    ReplyDelete
  4. How To Drop Indexes/ Unique Indexes In Oracle? >>>>> Download Now

    >>>>> Download Full

    How To Drop Indexes/ Unique Indexes In Oracle? >>>>> Download LINK

    >>>>> Download Now

    How To Drop Indexes/ Unique Indexes In Oracle? >>>>> Download Full

    >>>>> Download LINK

    ReplyDelete
  5. How To Drop Indexes/ Unique Indexes In Oracle? >>>>> Download Now

    >>>>> Download Full

    How To Drop Indexes/ Unique Indexes In Oracle? >>>>> Download LINK

    >>>>> Download Now

    How To Drop Indexes/ Unique Indexes In Oracle? >>>>> Download Full

    >>>>> Download LINK x7

    ReplyDelete
  6. At A2Z , take advantage of our first-rate movers and packers services to have a smooth and stress-free moving experience. As leaders in the field, we take great satisfaction in providing all-inclusive solutions that are customized to your specific moving requirements. Whether you’re moving locally or over a great distance, our team of pros is committed to making sure your relocation goes smoothly and safely. We do more than just move your stuff; we carefully pack, load, transport, unload, and unpack everything with the utmost care. Your belongings will be protected for the duration of the procedure thanks to the premium packaging supplies we utilize.http://a2zpackerandmover.com/services-best-packers-and-movers-in-kochi/

    ReplyDelete

Related Posts Plugin for WordPress, Blogger...

ShareThis