Primary Indexes and Primary AMP Indexes | VantageCloud Lake - Primary Indexes and Primary AMP Indexes - Teradata VantageCloud Lake

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 certain 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 typically 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 may use a secondary index, a join index, a full-table scan, or a mix of different index types.

• To provide for efficient joins.

  If there is an equijoin constraint on the PI or PA of a table, a direct join to the table may be possible. That is, rows of the table may not have to be redistributed, spooled,and sorted before 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, a more efficient aggregation may be possible.

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, Period, 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 join indexes only, the primary index can also be value-ordered.