16.20 - Primary Index Properties - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantageâ„¢ SQL Fundamentals

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Language
English (United States)
Last Update
2019-05-02
dita:mapPath
zce1519094756513.ditamap
dita:ditavalPath
TD_DBS_16_20_Update1.ditaval
  • Defined with the CREATE TABLE data definition statement.

    CREATE INDEX is used only to create secondary indexes.

  • Modified with the ALTER TABLE data definition statement.

    Some modifications, such as partitioning and primary index columns, require an empty table.

  • Automatically assigned by CREATE TABLE if you do not explicitly define a primary index. However, the best practice is to always specify the primary index, because the default may not be appropriate for the table.
  • Can be composed of as many as 64 columns.
  • A maximum of one can be defined per table.
  • Can be partitioned or nonpartitioned.

    Partitioned primary indexes are not automatically assigned. You must explicitly define a partitioned primary index.

  • Can be unique or nonunique.

    Note that a partitioned primary index can only be unique if all the partitioning columns are also included as primary index columns. If the primary index does not include all the partitioning columns, uniqueness on the primary index columns may be enforced with a unique secondary index on the same columns as the primary index.

  • Defined as nonunique if the primary index is not defined explicitly as unique or if the primary index is specified for a single column SET table.
  • Controls data distribution and retrieval using the Teradata Database hashing algorithm.
  • Improves performance when used correctly in the WHERE clause of an SQL data manipulation statement to perform the following actions.
    • Single-AMP retrievals.
    • Joins between tables with identical primary indexes, the optimal scenario.
    • Partition elimination when the primary index is partitioned.