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, Teradata Database implements it as a USI.
As a general guideline for decision support applications, 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.
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” on page 263.
Of course, you should always consider adding such constraints, including unique join indexes (see “Functions of Single-Table Join Indexes” on page 546), to your tables when they facilitate row access or joins. This is particularly true for NoPI tables, because specifying a USI in a request is the only way to access a single row in a NoPI table (see “NoPI Tables, Column‑Partitioned Tables, and Column-Partitioned Join Indexes” on page 280). This is true unless a NUSI happens to be defined that indexes only a single row.
All manner of database constraints are often useful for query optimization, and the richer the constraint set specified for a database, the more opportunities there are to enhance query optimization.
The likely benefits of adding uniqueness constraints are not restricted to multiset NUPI tables. It is also true that if you create a SET NUPI table, the system performs duplicate row checks by default unless you place a uniqueness constraint on the table. Unique constraint enforcement is often a less costly method of enforcing row uniqueness than system duplicate row checks.
Avoid defining a uniqueness constraint on the primary or alternate key of a multiset NUPI table solely to enforce row uniqueness because MultiLoad and FastLoad do not support target tables with non‑primary index uniqueness constraints. You can avoid MultiLoad and FastLoad problems associated with indexes by loading data into a table that is otherwise identical with the target table, but has no constraints or non‑primary indexes defined on it. After loading the data into that table, you can then INSERT … SELECT it into the target table that has the desired constraints and indexes defined on it.
Furthermore, USIs impose a performance cost because their index subtables must be maintained by the system as rows are inserted, deleted, and each time the column set in the base table they reference is updated.