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 as simple as follows:

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

In this syntax:

• 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.

If you attempt to drop a non-existing index, you will get an error.

Oracle does not provide the IF EXISTS option to drop an index conditionally.

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 statement 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 existCode language: JavaScript (javascript)

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