16.10 - Primary Indexes and Primary AMP Indexes - Teradata Database

Teradata Database Design
Product
Teradata Database
Release Number
16.10
Published
June 2017
Language
English (United States)
Last Update
2018-04-17
dita:mapPath
kae1480972952938.ditamap
dita:ditavalPath
changebar_rev_16_10_exclude_audience_ie.ditaval
dita:id
zqc1472244571611

Teradata Database tables can have a primary index, primary AMP index, or no primary index. If you do not define either PRIMARY INDEX, PRIMARY AMP INDEX, or NO PRIMARY INDEX explicitly when you create a table, then a default primary index for a table may be defined or the table may default to have no primary index (see Primary Index Defaults for details).

Use the CREATE TABLE statement to create primary indexes (see “CREATE TABLE” in SQL Data Definition Language).

Purposes of a Primary Index or Primary AMP Index

  • To define the distribution of the rows to the AMPs.

    With the exception of NoPI tables and join indexes, Teradata Database distributes table rows across the AMPs on the hash of their primary index or primary AMP index value. The determination of which AMP the row is to be stored on, is made solely on the value of the primary index or primary AMP index.

    The choice of columns for the primary index or primary AMP index affects how even this distribution is. An even distribution of rows to the AMPs is usually of critical importance in picking a primary index or primary AMP index column set.

  • To provide access to rows more efficiently than with a full-table scan.

    If the values for all the primary index or primary AMP index columns are specified in a DML statement, single-AMP access can be made to the rows using that primary index or primary AMP index value.

    With a row-partitioned primary index or primary AMP index, faster access is also possible when all the values of the partitioning columns are specified or if there is a constraint on partitioning columns. With a column-partitioned primary index or primary AMP index, only the column partitions with columns needed by the query are accessed.

    Other retrievals might use a secondary index, a hash or join index, a full-table scan, or a mix of several different index types.

  • To provide for efficient joins.

    If there is an equijoin constraint on the primary index or primary AMP index of a table, it may be possible to do a direct join to the table (that is, rows of the table might not have to be redistributed, spooled, and sorted prior to the join).

  • To provide a means for efficient aggregations.

    If the GROUP BY key is on the primary index or primary AMP index of a table, it is often possible to perform a more efficient aggregation.

Restrictions on Primary Indexes and Primary AMP Indexes

  • No more than one primary index or primary AMP index can be defined on a table.

    You can also define base data tables that do not have a primary index or primary AMP index (see NoPI Tables, Column-Partitioned NoPI Tables, and Column-Partitioned NoPI Join Indexes and Column-Partitioned NoPI Tables and Join Indexes).

    You cannot define a primary index or primary AMP index for a global temporary trace table. See “CREATE GLOBAL TEMPORARY TRACE TABLE” in SQL Data Definition Language for details.

  • No more than 64 columns can be specified in a primary index or primary AMP index definition.
  • Primary index or primary AMP index columns cannot be defined on columns that have XML, BLOB, CLOB, BLOB-based UDT, CLOB-based UDT, XML-based UDT, Period, ARRAY, VARRAY, VARIANT_TYPE, Geospatial, or JSON data types.
  • You cannot define a primary index or a primary AMP index for a NoPI table.
  • Primary index or a primary AMP index columns cannot be defined on row-level security constraint columns.
  • You cannot specify multivalue compression for primary index or a primary AMP index columns.

Primary Index Dimensions

Primary indexes are defined in two ways:

For hash and join indexes only, the primary index can also be value-ordered.