Why to drop index?

Let us assume a table has index created on it as per requirement. The same requirement got changed to add one more column to index or delete a column for index. So, we have to delete the existing table or view index and recreate as per new requirement.

DROP INDEX statement fulfils the requirement of dropping the existing index.

How to DROP Index?

DROP INDEX Statement used to delete the existing index on the table in the database. DROP INDEX syntax is not standard and can vary for different databases.

MS Access -

DROP INDEX index_name ON table_name; 

SQL Server -

DROP INDEX table_name.index_name; 

DB2/Oracle -

DROP INDEX index_name; 

MySQL -

ALTER TABLE table_name
 DROP INDEX index_name; 

To delete multiple Indexes on multiple tables –

DROP INDEX 
       index_name1 ON table_name1,
       index_name2 ON table_name2,
       ……………..; 

With IF EXISTS -

 DROP INDEX [IF EXISTS] Table_name.index_name; 

• table_name – Represents the name of the table.
• index_name – Represents the name of the index which is going to delete.

Scenario – Delete index on table.

Requirement – Delete existing index idx_eid on employee_details table. The query was as follows –

 DROP INDEX idx_eid; 

By using above query, we can delete the existing index on employee_details table.

scenario – Delete multiple indexes on multiple tables.

Requirement – Delete existing indexes on employee_details and employee_address tables. The query was as follows -

DROP INDEX idx_eid ON employee_details,
     idx_ename ON employee_address; 

By using above query, we can delete the existing indexes on employee_details and employee_address tables.

scenario – Trying to delete index on table without getting error if not exists.

Requirement – Delete index idx_eid on employee_details table. The query was as follows -

DROP INDEX [IF EXISTS] idx_eid; 

By using above query, we can delete the index idx_eid on employee_details table if exists. If the index does not exist, the system will not throw any error.