The Teradata Primary index is not an index in the
traditional sense, as it is not a lookup table. Instead, it is a mechanism that
defines where each data row is physically located on the Teradata system. The
primary index of a table may be defined as either a single column or as
multiple columns. The values of the primary index columns within the table may
be unique or non-unique.
The Primary Index of a table should not be confused
with the primary key of a table.The primary index is a part of the physical
database model, and affects the storage and retrieval of data rows. The primary
key is a part of the logical database model, and uniquely identifies each
record in the table. Often, the primary key of a table is a good candidate for
the primary index of a table, particularly for smaller “dimension” or “lookup”
tables, but this is not always the case for other tables.
There are two types of Primary Index. Unique
Primary Index ( UPI) and Non Unique Primary Index (NUPI). By default, NUPI is
created when the table is created. Unique keyword has to be explicitly given
when UPI has to be created.
UPI will slower the performance sometimes as for each and every row , uniqueness of the column value has to be checked and it is an additional overhead to the system but the distribution of data will be even.
We should be careful while choosing a NUPI so that the distribution of
data is almost even . UPI/NUPI decision should be taken based on the data and
its usage.
