How to Oracle DROP INDEX?

 Summary: in this tutorial, you will learn how to use the Oracle DROP INDEX statement to remove an index.

Introduction to Oracle DROP INDEX statement

The DROP INDEX statement allows you to drop an existing index. The syntax of the DROP INDEX statement is simple as follows:

DROP INDEX [schema_name.]index_name;
Code language: JavaScript (javascript)

First, specify an optional schema name to which the index belongs. If you omit the schema name, Oracle assumes that the index is in your own schema.

Second, specify the name of the index that you want to remove.

Oracle DROP INDEX IF EXISTS

An attempt to drop a non-existing index will result in an error.

Oracle does not provide the IF EXISTS option so that you can drop an index if it exists. To achieve this effect, you can use the following PL/SQL anonymous block:

DECLARE index_count INTEGER;
BEGIN
SELECT COUNT(*) INTO index_count
    FROM USER_INDEXES
    WHERE INDEX_NAME = 'index_name';

IF index_count > 0 THEN
    EXECUTE IMMEDIATE 'DROP INDEX index_name';
END IF;
END;
/
Code language: JavaScript (javascript)

Oracle DROP INDEX example

The following statement removes the member_name_i index from the members table:

DROP INDEX members_name_i;
Code language: JavaScript (javascript)

Note that if you try to drop a non-existing, you will get the following error:

SQL Error: ORA-01418: specified index does not exist
Code language: JavaScript (javascript)

In this tutorial, you have learned how to use the Oracle DROP INDEX statement to remove an index.