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.
- 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 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
- 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.
- Delete secondary index references to the current value.
- 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.
