17.10 - Unique Secondary Indexes - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Database Design

Advanced SQL Engine
Teradata Database
Release Number
July 2021
English (United States)
Last Update

USIs are useful both for base table access (because USI access is, at worst, a two-AMP operation) and for enforcing data integrity by applying a uniqueness constraint on a column set. Like a unique primary index, a unique secondary index can be used to guarantee row uniqueness.

Using Unique Secondary Indexes to Enforce Row Uniqueness

When a non-primary index uniqueness constraint is created, whether it is a PRIMARY KEY or UNIQUE constraint, Vantage implements it as a USI.

As a general guideline, whenever you define a primary index for a multiset table to be a NUPI, particularly if the table is created in ANSI/ISO session mode (where the default for tables is multiset), you should consider defining one of the following uniqueness constraints on its primary key or other alternate key to facilitate row access and joins.
  • Unique secondary index
  • UNIQUE NOT NULL constraint
  • PRIMARY KEY NOT NULL constraint

    PRIMARY KEY and UNIQUE constraints are both mapped internally as USIs unless they are used to define the default UPI for a table. See Primary Index Defaults.

USI Access

USI access is usually a two-AMP operation because Vantage typically distributes a USI row to a different AMP than the base table row the index points to. If the system distributes the USI subtable row to the same AMP as the base table row it points to, then only one AMP is accessed (but it is still a two-step operation).

The following stages are involved in a USI base table row access.
  • The requested USI value is accessed by hashing to its subtable.
  • The pointer to the base table row is read and used to access the stored row directly.

Unique Secondary Indexes and Performance

Statistics play an important part in optimizing access when USIs define conditions for the following operations.
  • Joining tables
  • Satisfying WHERE predicates that specify comparisons, string matching, or complex conditionals
  • Satisfying LIKE expressions
  • Processing aggregates

Because of the additional overhead for index maintenance, USI values should not change frequently. When you change the value of a secondary index, Vantage must undertake the following maintenance operations.

  1. Delete secondary index references to the current value.
  2. Generate secondary index references to the new value.

Creating a Unique Secondary Index as a Composite of a Row-Level Security Constraint Column and a NUPI Column Set

You can create a USI for a row-level security-protected table as a composite of a row-level security constraint column and the columns of a NUPI for the table. This property can be used to implement polyinstantiation.

Polyinstantiation is a property that allows a relation to contain multiple rows with the same primary key value, where the multiple instances are distinguished by their security levels, where a security level is defined by a row-level security constraint column.

Restrictions on Load Utilities

You cannot use FastLoad, MultiLoad, or the Teradata Parallel Transporter operators LOAD and UPDATE to load data into base tables that have unique secondary indexes.

Before you can load data into a USI-indexed base table, you must first drop all defined USIs before you can run FastLoad, MultiLoad, or the Teradata Parallel Transporter operators LOAD and UPDATE.

Load utilities like Teradata Parallel Data Pump, BTEQ, and the Teradata Parallel Transporter operators INSERT and STREAM, which perform standard SQL row inserts and updates, are supported for USI-indexed tables.

USI Hashing

USIs are hash-partitioned on their index columns, as indicated by the following graphic.

USI hashing on index columns