15.00 - Principal Criteria for Selecting a Primary Index - Teradata Database

Teradata Database Design

Teradata Database
User Guide

Principal Criteria for Selecting a Primary Index

When assigning columns to be the primary index for a table, there are three essential factors to keep in mind: uniform distribution of rows, optimal access to the data, and the volatility of indexed column values.

You will sometimes encounter situations where the selection criteria conflict. For example, specifying a NUPI instead of a UPI, or specifying an alternate key as the UPI instead of the primary key.

There are additional criteria to evaluate when selecting the primary index for a queue table. See “Selecting a Primary Index for a Queue Table” on page 421 for a description of the primary index selection criteria you need to evaluate when choosing a primary index for a queue table.

Keep in mind that these criteria apply only to selecting a column set for the primary index. They do not apply to making a decision whether the primary index should be row‑partitioned or not. See “Row‑Partitioned and Nonpartitioned Primary Index Access for Typical Operations” on page 403 and “Considerations for Choosing a Primary Index” on page 410 for guidelines on making that choice.

Be aware that with the exception of column‑partitioned tables, Teradata Database assigns a default primary index to a table if you do not specify an explicit PRIMARY INDEX or NO PRIMARY INDEX in the CREATE TABLE request you use to create the definition for the table (see “Primary Index Defaults” on page 263).