DROP INDEX Usage Notes - Teradata VantageCloud Lake

Reasons to Drop a Secondary Index

EXPLAIN reports 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 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 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.
It can be more efficient to recreate an index than to update it in parallel with the updates made to the base data table or join index on which it is defined.

UNIQUE and PRIMARY INDEX Constraints

If a UNIQUE or PRIMARY KEY constraint has been created implicitly as a USI, you can remove it using a DROP INDEX statement that explicitly references the column set on which the constraint is defined.

Restrictions on Dropping a Secondary Index on a Table Concurrent with Dynamic AMP Sample Emulation on that Table

You cannot drop a secondary index for a table while that table is subject to dynamic AMP sample emulation. To disable dynamic AMP sampling, contact Teradata Support.

To use dynamic AMP sampling on the table from which you dropped the secondary index, use the following general procedure:

Drop the secondary index from the table on the target system.
Extract a fresh dynamic AMP sample from the target system.
Apply the fresh sample to the source system.

Considerations for Dropping Secondary Indexes to Enable Client Batch Load Utilities

You cannot drop a secondary index to enable batch data loading by utilities such as MultiLoad and FastLoad if queries are running that access that secondary index. If a DROP INDEX transaction is running, batch data loading jobs against the underlying table of the index cannot begin processing because of the EXCLUSIVE lock DROP INDEX places on the base table that defines the index.