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 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 do this, 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)

Summary #

• Use the Oracle DROP INDEX statement to remove an index from the Oracle database