How to Drop the index in Oracle

In this post, we will discuss the oracle drop index, how to Drop the index in Oracle, Drop Oracle Index if exists, and How to drop unique index in oracle

How to Drop the index in Oracle

• You can drop an index in oracle using the DROP INDEX command.
• It removes the named index.

General Syntax

drop index  <schema name>.<index name>  ;

Here schema name is the owner of the index and the 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

Examples

drop index  <index name>  ;
drop index EXP_IDX1;
drop index SCOTT.EXP_IDX1

• You should be careful while dropping the index as it may lead to bad performance in queries where it is used.
• When the index is dropped, all extents of the index segment are returned to the containing tablespace and become available for other objects in the tablespace.

Privileges required

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 the “drop any index” system privilege 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 made by defining a UNIQUE key constraint or primary key constraint 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

• You must disable or drop the constraint itself to drop a constraints-associated index. 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 it with the drop index command

drop all indexes on a table oracle

We can drop all the indexes in the table using the below SQL

spool drop_table_index.sql
select 'drop index '||index_name||';' from user_indexes WHERE table_name = '&table_name'
AND index_name NOT IN ( SELECT distinct index_name FROM user_constraints WHERE table_name = '&table_name' AND index_name IS NOT NULL);
spool off
@drop_table_index.sql

This will drop all the indexes except the unique key/primary key constraint index. Those can be done using the technique given above

We can also use the below PLSQL procedure also if it is the preference

SET SERVEROUTPUT ON
DECLARE
c_ind varchar2(30);
cursor c1 is
SELECT index_name
FROM user_indexes
WHERE table_name = '&table_name'
AND index_name NOT IN
(
SELECT distinct index_name
FROM user_constraints
WHERE table_name = '&table_name'
AND index_name IS NOT NULL
)
BEGIN
for rec in c1;
LOOP
execute immediate 'DROP INDEX '||rec.index_name;
END LOOP;
END;
/

how to drop all indexes in oracle schema

spool drop_schema_index.sql
select 'drop index '||index_name||';' from user_indexes where index_name NOT IN
(
SELECT distinct index_name
FROM user_constraints
WHERE index_name IS NOT NULL
);
spool off
@drop_schema_index.sql

drop index oracle force

We can use the force options for the spatial index

DROP INDEX [schema.]index [FORCE];

Force Causes the spatial index to be deleted from the system tables even if the index is marked in-progress or some other error condition occurs.

Hope you like this article. Please do provide the feedback

 Related Articles

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, limitations, advantages 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
oracle drop table if exists: Check how to check if table exists and then drop the table in oracle
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_8015.htm