15.00 - Mandatory Use of Secondary Indexes - Teradata Database

Teradata Database Design

Teradata Database
User Guide

Mandatory Use of Secondary Indexes

Secondary indexes are never required and generally are not recommended for tables that are accessed by OLTP applications.

They are highly recommended for decision support applications that return multiple rows by design because they often provide superior performance to full‑table scans.

While UPIs are supported for FastLoad and MultiLoad, USIs are not. As a general rule, therefore, a table that is frequently updated using these utilities should not be defined with USIs. Otherwise, you must drop the USIs before the load operation begins and then recreate them after the load completes. You can often substitute a MERGE request for a MultiLoad job, and MERGE does support loading into target tables defined with USIs (see “MERGE” in SQL Data Manipulation Language). Note that you can create a USI for a table explicitly (see “Using Unique Secondary Indexes to Enforce Row Uniqueness” on page 457 for details) or implicitly with a PRIMARY KEY or UNIQUE constraint when the constraint is not implemented as a UPI (for details, see “PRIMARY KEY Constraints” on page 654, “UNIQUE Constraints” on page 656, and “Primary Index Defaults” on page 263).

Note, too, that FastLoad does not support NUSIs (secondary indexes, if needed, must be dropped or not created before the load and created after the FastLoad). See Chapter 10: “Secondary Indexes,” for details.