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:
