15.00 - Primary Indexes - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1184-015K
Language
English (United States)

Primary Indexes

Teradata Database hashes the primary index value set to determine on which AMPS the rows of a primary‑indexed table are to be stored. Unpartitioned NoPI tables and column‑partitioned tables and join indexes, which do not have a primary index, are distributed to the AMPs using non‑hashing mechanisms. See Database Design for information about how the rows of tables and join indexes that do not have a primary index are distributed to the AMPs.

Selection and update operations that use the primary index column in a WHERE clause use hashing to determine the location of any row with maximum efficiency.

You cannot compress the values of columns that are members of the primary index column set, nor can you compress the values of the columns of a partitioning expression.

You cannot include BLOB, CLOB, JSON, XML, BLOB‑based UDT, CLOB‑based UDT, XML‑based UDT, Geospatial, ARRAY, VARRAY, Period, or row‑level security constraint columns in the primary index column set, nor can you include row‑level security constraint columns. This means that any table that contains BLOB, CLOB, Geospatial, ARRAY, VARRAY, Period, or row‑level security constraint columns must contain at least 1 other non‑BLOB, non‑CLOB, non‑Geospatial, non‑ARRAY/VARRAY, or non‑Period column that can be used to define its primary index.