Primary Indexes and Primary AMP Indexes | Teradata Vantage - Primary Indexes and Primary AMP Indexes - Teradata Vantage - Analytics Database

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 some join indexes, Vantage distributes table rows across the AMPs based on the hash of their PI or PA value. The choice of columns for the PI or PA affects how even this distribution is. An even distribution of rows to the AMPs is usually critical in picking a PI or PA column set.

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

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

  With a row-partitioned object, faster access is also possible when values of partitioning columns are specified or if there are constraints on partitioning columns. With a column-partitioned object, 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 PI or PA 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 for efficient aggregations.

  If the grouping defined by a GROUP BY clause is on the PI or PA 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 PI or PA can be defined on a table.

  You can also define tables that neither have a PI nor a PA (see NoPI Tables, Column-Partitioned NoPI Tables, and Column-Partitioned NoPI Join Indexes and Column Partitioning).

• No more than 64 columns can be specified in a PI or PA definition.
• A PI and PA column cannot have an XML, BLOB, CLOB, BLOB-based UDT, CLOB-based UDT, XML-based UDT, Period, ARRAY, VARRAY, VARIANT_TYPE, Geospatial, or JSON data type.
• A PI and PA column cannot be a row-level security constraint column
• You cannot specify multivalue compression for a PI or PA column

Primary Index Dimensions

See Partitioned and Nonpartitioned Primary Indexes, Single-Level Partitioning, and Multilevel Partitioning.

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