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.

Here’s the basic syntax of the DROP INDEX statement:

DROP INDEX index_name;Code language: SQL (Structured Query Language) (sql)

DROP INDEX schema_name.index_name;Code language: SQL (Structured Query Language) (sql)

If you attempt to drop an index that does not exist, 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 #

First, create a table called vendors:

CREATE TABLE IF NOT EXISTS vendors(
    id NUMBER PRIMARY KEY,
    name VARCHAR2(100) NOT NULL,
    address VARCHAR2(255)
);Code language: SQL (Structured Query Language) (sql)

Second, create an index for the name column of the vendors table:

CREATE INDEX vendors_name_i
ON vendors(name);Code language: JavaScript (javascript)

Third, drop the vendors_name_i index from the vendors table:

DROP INDEX vendors_name_i;Code language: SQL (Structured Query Language) (sql)

Output:

Index VENDORS_NAME_I dropped.Code language: SQL (Structured Query Language) (sql)

If you attempt to drop an index that does not exist, you’ll encounter an error. For example:

DROP INDEX vendors_code_i;Code language: SQL (Structured Query Language) (sql)

Since the index vendors_code_i does not exist, Oracle issues the following error:

ORA-01418: specified index does not existCode language: JavaScript (javascript)

Summary #

• Use the Oracle DROP INDEX statement to remove an index from a table.

Quiz #