Principal Criteria for Selecting a Primary Index - Teradata Database

Teradata Database Design

Product
Teradata Database
Release Number
15.10
Language
English (United States)
Last Update
2018-10-06
Product Category
Software

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 369 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 351 and “Considerations for Choosing a Primary Index” on page 358 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 211).