Primary Index

• Teradata uses the Primary Index of each table to provide a row its destination to the proper AMP.
• This is why each table in Teradata is required to have a Primary Index.
• The biggest key to a great Teradata Database Design begins with choosing the correct column to be the Primary Index.
• The Primary Index value for a row is the only thing that will determine on which AMP a row will reside.
• The Primary Index determines data distribution and provides the fastest physical path to retrieving data.
• Primary Index also plays an incredibly important role in how joins are performed.
• Every table must have one and only one Primary Index. Because Teradata distributes the data based on the Primary Index columns value.
• The Primary index is the Physical Mechanism used to retrieve and distribute data.
• The primary index is limited to the number of columns in the primary index. This means that the primary index is comprised totally of all the columns in the primary index.
• You can have up to 64 multi-column keys comprising your primary index or as little as one column as your primary index.

Two Types of Primary Indexes (UPI or NUPI)

1) Unique Primary Index(UPI).

2) Non- unique primary Index(NUPI).

Unique Primary Index (UPI)

• A Unique Primary Index means that the values for the selected column must be unique.
• A Unique Primary Index will always spread the table rows evenly amongst the AMPs.
• UPI access is always a one-AMP operation.
• If we are using UPI value in 'where' condition or Join condition, no need of data redistribution, so performance will be very high.

Non-Unique Primary Index (NUPI)

• Non-Unique Primary Index (NUPI) means that the values for the selected column can be non-unique.
• Duplicate values can exist.
• NUPI will almost never spread the table rows evenly.

Multi-Column Primary Indexes

• A Primary Index can have multiple columns.
• Still it is consider as only one Primary Index(combining multiple columns together).
• Teradata allows up to 64 combined columns to make up the one Primary Index required for a table.

Two benefits:

1. To get better data distribution among the AMPs.
2. Users often use multiple keys consistently to query.

When do you define the Primary Index?

•  When the table is created it is given a table name, the columns and their data types are defined and the Primary Index is specified.
• If no Primary Index is defined the system will define one for you.
• It will most likely pick the first column and make it a Non-Unique Primary Index (NUPI).
• It will however check to see if you have a Primary Key defined for referential integrity purposes. If you do it will choose that column(s) and make it a Unique Primary Index (UPI).
• If you didn’t define a Primary Index or Primary Key then the system will check to see if you defined a Unique Secondary Index (USI) on any column and if you have it will make that column a Unique Primary Index (UPI).