The best explained Teradata Unique Secondary Index USI

teradata unique index

ON tbl_employee;

If a Teradata SQL request uses secondary index values in a WHERE constraint, the optimizer may use the rowID in a secondary index subtable to access the qualifying rows in the data table.

If a secondary index is used only periodically by certain applications and is not routinely used by most applications, disk space can be saved by creating the index when it is needed and dropping it immediately after use.

A unique secondary index is very efficient, it typically allows access of only two AMPs, requires no spool file, and has one row per value, therefore, when a unique secondary index is used to access a row, two AMPs are involved.

Unique secondary indexes can thus improve performance by avoiding the overhead of scanning all AMPs.

Relates Posts


Popular posts from this blog

SQL for Quantile Function in Teradata

3 Uses of SAMPLE function in Teradata

3 Types of Temporal Tables in Teradata