In Oracle databases, an index refers to a data structure that advances the speed of data retrieval operations on a database table. However, this may come with a penalty of extra write operations and storage space on your database.
In this tutorial, you will learn how to drop an existing index from a database table using the DROP INDEX command.
Oracle DROP INDEX Command
The following demonstrates the syntax of the DROP INDEX command in Oracle databases:
Substitute index_name with the name of the index you wish to drop.
If the index you want to drop is in a different schema, you will need to specify the schema name in the DROP INDEX statement. For example:
In this case, you need to replace schema_name with the schema’s name where the index resides.
Oracle Create Sample Index
The example statement below creates a sample index on the employees’ table for illustration purposes.
This section does not cover the details of creating indexes in Oracle databases. Check our tutorial on the Oracle Create Index command to learn more.
Once the index is created, we can remove it as shown in the section below:
Oracle Drop Index
Use the DROP INDEX statement followed by the index name to drop the above index, as shown:
The query above will remove the index called multi_lookup from the current schema.
Oracle Drop Index – Conditional
In Oracle, attempting to remove a non-existent index will result in an error. Take the example query below:
Since the multi_lookup index does not exist, the query will return an error:
In some database engines, we could overcome this by adding the IF NOT EXISTS clause, which prevents the statement from returning an exception if the error does not exist.
Unfortunately, some older versions of Oracle do not have the IF EXISTS command. We can, however, implement custom logic for preventing exceptions if the index does not exist, as shown:
BEGIN
SELECT COUNT(*) INTO index_num
FROM USER_INDEXES
WHERE INDEX_NAME = 'index_name';
IF index_count > 0 THEN
EXECUTE IMMEDIATE 'DROP INDEX index_name';
END IF;
END;
This code uses a PL/SQL block to drop an index if it exists. We start by declaring a variable called index_num to store the number of indexes with the given name.
Next, we use a SELECT statement to count the number of indexes with the given name in the USER_INDEXES table and store the result in the index_num variable.
We then use an IF block to check if the value of the index_num variable is greater than 0. If true, we remove all the indexes with the specified name. Otherwise, we fail to call the remove index statement preventing any exceptions.
Conclusion
In conclusion, the DROP INDEX statement removes an index from a database table in Oracle. The syntax for this statement is:
To drop an index in a different schema, you can use the following syntax:
You can use the IF EXISTS clause in the DROP INDEX statement to prevent an exception from being thrown if the index does not exist. For example:
Alternatively, as shown in the previous examples, you can use a PL/SQL block to check if the index exists before attempting to drop it.
