15.00 - Using Unique Secondary Index Maintenance and Rollback to Optimize Query Design - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

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.