You can drop an index in oracle using the DROP INDEX command.
It removes the named index.
The general Syntax is
drop index <schema name>.<index name> ;
Here schema name is the owner of the index and index name is the index name. If you don’t provide the schema name, then it is assumed the index exists in the current schema
Example of drop index oracle
drop index <index name> ; drop index EXP_IDX1; drop index SCOTT.EXP_IDX1
- You should be carefully while dropping the index as we may leads to bad performance in queries where it is used.
- When you drop an index, all extents of the index segment are returned to the containing tablespace and become available for other objects in the tablespace.
If you are the owner of the index, then there are no extra privileges required. But if you want to drop the index in a different schema, then drop any index system privileges is required
Drop Oracle Index if exists
Oracle does not provide any clause such as if exists for drop index. You can implement this functionality using the below PLSQL block
SET SERVEROUTPUT ON DECLARE c_ind int:=0; BEGIN SELECT count(*) into c_ind FROM user_indexes where index_name = 'index_name'; if c_emp > 0 EXECUTE IMMEDIATE 'drop index index_name'; end if; END; /
How to drop unique index in oracle
- you can’t drop any implicitly created index, such as those created by defining a UNIQUE key constraint or primary key constaint on a table, with the drop index command. If you try to do so it will throw an error.
ORA-02429: CANNOT DROP INDEX USED FOR ENFORCEMENT OF UNIQUE/PRIMARY KEY
- To drop a constraints associated index, you must disable or drop the constraint itself.You can check the constraint using the below query
SELECT OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, INDEX_OWNER, INDEX_NAME FROM ALL_CONSTRAINTS WHERE INDEX_NAME = '&INDEX_NAME'
Then you can drop the constraint using the below sql
ALTER TABLE <table name> DROP CONSTRAINT <constraint name> ;
- If you have created the unique index explicitly, then you should be able to drop with drop index command
Hope you like this article on the oracle drop index
external tables in Oracle: Check out this post for information on the usage of the external tables in oracle with an example, how to create an external table, how to use it
Oracle Create table: Tables are the basic unit of data storage in an Oracle Database. we cover how to use Oracle create table command to create a table with a foreign key /primary key
oracle create tablespace statement: This article on how to create tablespace in oracle, various characteristics associated with it and different create tablespace statements
Find indexes status and assigned columns for a table
Virtual Index in Oracle: What is Virtual Index in Oracle? Uses, limitation, advantage and how to use to check to explain plan in Oracle database, Hidden parameter _USE_NOSEGMENT_INDEXES
Oracle Index clustering factor: How Oracle Index clustering factor is calculated and how it impacts the explain plan
Oracle Partition Index: Understanding Oracle partition index, What is Global Non-partitioned Indexes?, What is local prefixed indexes, non-prefixed local index