Practical SQL Handbook, The: Using SQL Variants, Fourth Edition

Removing Indexes

There are two situations in which you might want to drop an index:

• You or someone else has created an index on a column, but the index is not used for most or all queries.

• You are about to issue a large number of data modification statements that will change key values. Because the system may have to do a lot of work to keep the index up-to-date, it might be more efficient to drop the index and then re-create it (with the CREATE INDEX statement) after changing the data.

It may still take some time to rebuild the index later, but you may prefer to postpone the lag time rather than endure a degradation in the system's performance during the data modifications. If one of the purposes of the index is to guarantee uniqueness, find ...