There are several reasons you might want to drop a secondary index:
- EXPLAIN reports might indicate that access patterns for a table have changed and the index is no longer selected by the Optimizer.
When this happens, you not only waste storage resources by retaining the index, but you also impact system performance by maintaining it.
- You cannot use MultiLoad to load rows into a table that has a USI, so you might need to drop any USIs defined on a table so you can load new rows into.
After the MultiLoad operation completes, you can then recreate the index.
- The performance of some maintenance operations and large update jobs can be better when no secondary indexes are defined on the affected table because of the necessary parallel maintenance that must be done on secondary index subtables.
When you encounter this issue, it can be more high-performing to drop the index, perform the maintenance or update task, and then recreate the index afterward because in many cases, it is more efficient to recreate an index than it is to update it in parallel with the updates made to the base data table or join index on which it is defined.