Purposes of the Primary Index
With the exception of NoPI tables and column‑partitioned tables and join indexes, Teradata Database distributes table rows across the AMPs on the hash of their primary index value (see “Teradata Database Hashing Algorithm” on page 225). The determination of which hash bucket, and hence which AMP the row is to be stored on, is made solely on the value of the primary index.
The choice of columns for the primary 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 column set.
If the values for all the primary index columns are specified in a DML statement, single‑AMP access can be made to the rows using that primary index value.
With a row partitioned primary 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.
Other retrievals might use a secondary index, a hash or join index, a full‑table scan, or a mix of several different index types.
If there is an equijoin constraint on the primary 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).
If the GROUP BY key is on the primary index of a table, it is often possible to perform a more efficient aggregation.