Master the Differences between Teradata Primary and Secondary Index
None of these indices is mandatory. Nevertheless, in most cases, a primary index should be defined.
A table can have a maximum of one primary index, but up to 32 secondary indexes.
Both the primary index and the secondary index can consist of up to 64 columns.
Both the primary index and the secondary index can be unique or non-unique. However, the distribution of rows for a non-unique secondary index (NUSI) is different (it is not distributed according to rowhash).
You can drop a secondary index at any time and create it again with a different definition.
The column set of a primary index cannot be changed. However, you can change it from NUPI to UPI, for example:
ALTER TABLE Customer MODIFY UNIQUE PRIMARY INDEX (>NUPI columns>)
The primary index deteriorates performance only if the distribution is skewed. No maintenance is needed.
A secondary index deteriorates performance because it is mapped in a sub table, which must be maintained with every UPDATE and INSERT statement. This means that a secondary index should only be created if it is also used!
The primary index does not need its own data structure since it simply distributes the rows according to a hashing algorithm.
The secondary index is stored in a sub table.
Row-level partitioning is only available for the primary index.
The rows are sorted by a rowhash of the primary index of an unpartitioned table. If the table is partitioned, it is sorted by rowhash after the rows have been assigned to the correct partitions.
For a non-unique secondary index, the rows can be sorted by the rowhash or by value (INTEGER datatype only). The unique secondary index can only be sorted by rowhash.