15.00 - Primary Index Properties - Teradata Database

Teradata Database Design

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

Primary Index Properties

  • Teradata Database uses the primary index of a table to control its row distribution and retrieval using the Teradata Database hashing algorithm (see “Teradata Database Hashing Algorithm” on page 225).
  • The primary index for an unpopulated table is defined using the CREATE TABLE data definition statement (see the documentation for the CREATE TABLE, CREATE HASH INDEX, and CREATE JOIN INDEX statements in SQL Data Definition Language).
  • CREATE INDEX is used only to create secondary indexes.

  • The primary index for an empty table can be modified, with restrictions, using the ALTER TABLE data definition statement (see the documentation for the ALTER TABLE statement in SQL Data Definition Language)
  • If no explicit primary index is defined in a CREATE TABLE request, the system determines whether the table has no primary index or to assign one automatically according to the rules described in “Primary Index Defaults” on page 263.
  • A primary index can be unique or nonunique (see “Unique Primary Indexes” and “Nonunique Primary Indexes” on page 265).
  • If the primary index is not defined explicitly as unique, then its definition defaults to nonunique (see “Primary Index Defaults” on page 263).
  • A primary index can be row‑partitioned or nonpartitioned (see “Row-partitioned Primary Indexes” and “Nonpartitioned Primary Indexes” on page 267).
  • If row‑partitioned, a primary index can be partitioned at a single level or at multiple levels (see “Single‑Level Partitioning” on page 362 “Multilevel Partitioning” on page 372).

    A primary index of a hash or join index can also be value‑ordered, subject to constraints on the data type and field length of the ordering column. See the documentation for CREATE HASH INDEX and CREATE JOIN INDEX in SQL Data Definition Language for details.

  • A primary index can be composed of as many as 64 columns (see “Restrictions on Primary Indexes” on page 262).
  • The values for a primary index can be generated automatically if defined on an identity column with the characteristics ALWAYS GENERATED and NO CYCLE (see “Identity Columns” on page 818).
  • A primary index cannot contain columns with a Period, Geospatial, JSON, ARRAY, VARRAY, VARIANT_TYPE, XML, BLOB, CLOB, XML‑based UDT, BLOB‑based UDT, or CLOB‑based UDT data type.
  • Distinct and structured UDT columns are valid components of a primary index. UDT columns based on internal Teradata UDT types (such as Period, ARRAY, VARRAY, and geospatial) are not valid components of a primary index.

  • Zero or one primary index must be specified per base table or join index you create (see “Restrictions on Primary Indexes” on page 262).
  • The following table types are the only exceptions to the rules that a table or join index must have a primary index.

  • Global temporary trace tables.
  • You cannot define a primary index or any other kind of index on a global temporary trace table. See “CREATE GLOBAL TEMPORARY TRACE TABLE” in SQL Data Definition Language Detailed Topics for details.

  • Nonpartitioned NoPI tables.
  • See “NoPI Tables, Column‑Partitioned Tables, and Column-Partitioned Join Indexes” on page 280 for details.

  • Column‑partitioned tables and join indexes.
  • See “Column‑Partitioned Tables and Join Indexes” on page 285 for details.

  • At most, one primary index can be defined per table or join index (see “Restrictions on Primary Indexes” on page 262).
  • A primary index improves performance when specified correctly in the WHERE clause of an SQL data manipulation request to perform the following actions (see “Purposes of the Primary Index” on page 262):
  • Single-AMP retrievals
  • Joins between tables with identical primary indexes, the optimal scenario.
  • Eliminate row partitions when selecting from and joining partitioned tables for various types of range queries.
  • Related Topics

     

    Topic

    Reference

    System‑derived PARTITION and PARTITION#Ln columns

    “PARTITION Columns” on page 801

    Primary indexes of temporal tables.

    ANSI Temporal Table Support and Temporal Table Support

    Row partition elimination

    SQL Request and Transaction Processing

    Primary indexes and their defaults, restrictions, and uses for:

  • ALTER TABLE
  • CREATE HASH INDEX
  • CREATE JOIN INDEX
  • CREATE TABLE
  • SQL Data Definition Language