15.00 - Criteria for Selecting a Primary Index - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

Criteria for Selecting a Primary Index

The following guidelines and performance considerations apply to selecting a unique or a nonunique column set as the primary index for a table.

  • Choose columns for the primary index based on the selection set most frequently used to retrieve rows from the table even when that set is not unique (if and only if the values of the selection set are fairly equally distributed across the AMPs).
  • Choose columns for the primary index that do not have XML, BLOB, CLOB, BLOB‑based UDT, CLOB‑based UDT, XML-based UDT, Period, JSON, ARRAY, VARRAY, or VARIANT_TYPE data types.
  • Distinct and structured UDT columns are valid components of a primary index, but UDT columns based on internal Teradata UDT types, such as the Period, Geospatial, ARRAY, and VARRAY types, are not.

  • Choose columns for the primary index that distribute table rows evenly across the AMPs. The more singular the values for a column, the more optimal their distribution.
  • Choose as few columns as possible for the primary index to optimize its generality.
  • All the columns in a composite primary index must be specified in a WHERE clause predicate before the Optimizer can select it for use as the retrieval mechanism.

  • If it is difficult to define a unique primary index for a table that must have one, you can generate arbitrary unique values for a single column if you define it as an identity column with the characteristics ALWAYS GENERATED and NO CYCLE (see “Identity Columns” on page 818).
  • Base the column selection on an equality search (if the primary index is a PPI, then the search is done within each non-eliminated populated partition). For equality constraints only, the system hashes directly to the row set that satisfies the condition.
  •  

    Tables with this kind of primary key …

    Tend to assign the primary index to …

    single‑column

    the primary key.

    This is referred to as a Unique Primary Index (UPI).

    multicolumn

    one of the foreign key components of the primary key.

    This is referred to as a Non‑Unique Primary Index (NUPI).

  • Primary and other alternate key column sets often can provide useful uniqueness constraints as well as a powerful access and join method when the logical design for a table is physically realized. If the primary or other alternate keys for a table are not selected to be its primary index, you should consider assigning a unique constraint, such as PRIMARY INDEX, UNIQUE, or a USI on those keys if the uniqueness constraint would facilitate table access and joins.
  • This recommendation is contingent on a number of complicated factors that must be considered fully before implementing unique constraints. See “Using Unique Secondary Indexes to Enforce Row Uniqueness” on page 457 for a list of the factors that should be considered when you consider implementing this recommendation.

     

    A UPI …

    WHILE a NUPI …

    at most involves one row

    can involve multiple rows.

    does not require a spool

    often creates a spool.

  • Duplicate NUPI values are always stored on the same AMP and in the same data block if possible.
  • NUPI retrieval only requires one I/O operation (or two I/Os if the cylinder index is not memory-resident) when the rows are stored in the same data block.
  •  

    This type of value range …

    Seen when using this predicate in a WHERE clause …

    Results in this kind of retrieval action …

    implicit

    BETWEEN

    full table scan, irrespective of any indexes defined for the table.

    The exceptions are the following:

  • PPI tables and join indexes, where row partition elimination can be exploited.
  • Hash and join index tables with a value‑ordered NUPI, where value ordering can be exploited.
  • explicit

    IN

    individual row hashing.