Using Unique Secondary Index Maintenance and Rollback to Optimize Query Design
Teradata Database processes USI maintenance operations (INSERT … SELECT, full-file DELETE, join DELETE, and UPDATE) block-at-a-time rather than row-at-a-time, whenever possible.
When the original index maintenance is processed block-at-a-time, the USI change rows are transient journaled block-at-a-time. As a result, the rollback of the USI change rows are block‑at-a-time, that is, block optimized.
USI change rows are redistributed to the owner AMP, sorted, and applied block-at-a-time to the USI subtable, such that the index data blocks are updated once rather than multiple times.