It is a field or set of fields that will help the Teradata to store records in a distributed fashion among the AMPs. Every table is mandated at least one column as the primary index. The primary index is fixed when the table is created. It cannot be altered after creation and hence for the alteration of PI it table should be recreated.

Purpose of Primary Index :

To explain the distribution of the rows to the AMPs.
To provide access to rows more efficiently than with a full‑table scan.
To provide for efficient joins.
To provide a means for efficient aggregations.


ROW ID   =  Rowhash    +   Unique Value

Row Value     =        Row ID    Appending to     Data

Row Hash: It will be calculated by the Parsing engine passing respective row to the Hashing algorithm.

Unique Value: It is generally used for UPI (the value incremented based if two columns has the same value.

When a row is inserted into a table, the file system stores the 32-bit row hash value of the primary index primary indexed Teradata Database table rows are self-indexing based on their primary index and so takes no additional storage space.

For Unique Primary Index Unique value is 0 and for Non Unique primary Index value is incremented in ascending order for same row hash values.


Types of Primary Index : There are two types of Primary Index


  •         Unique Primary Index
  •         Non Unique Primary Index

Hashing in Primary Index :


     
Limitations for Primary Index:

                 1) One  PRIMARY INDEX per table.
                 2) PRIMARY INDEX value can be UNIQUE OR NON-UNIQUE.
                 3) PRIMARY INDEX value can be null.
                 4) PRIMARY INDEX value can be modified.
                 5) PRIMARY INDEX of a populated table can not be modified.
                 6) PRIMARY INDEX has a limit of 64 columns.