Use the DROP INDEX statement to destroy an index. As an index is logically and physically independent of the data in its associated table, you can drop the index at any time without affecting the table (or other indexes). All SQL programs and other applications continue to work if you drop an index, but access of previously indexed data will be slower.
The usual reasons for dropping an index are:
The index is no longer needed.
The extra time it takes the DBMS to maintain the index after INSERT, UPDATE, or DELETE operations outweighs the speed improvement in retrieval operations that the index provides.
The SQL-92 standard omits indexes, so index-related SQL statements vary by DBMS. I’ll describe how to drop an ...