By introduction, I would like to point out that the Teradata primary index should not be confused with a primary key. The primary index is only used to distribute the data evenly across all AMPs. Often the primary index will be equal to the primary key because it has primary key properties that often make it appear optimal as a primary index.
Is a Teradata Primary Index or Secondary Index required?
None of these indices is mandatory. Nevertheless, in most cases, a primary index should be defined.
How many indexes can be defined per index type?
A table can have a maximum of one primary index but up to 32 secondary indexes.
How many columns can the indexes consist of?
Both the primary and secondary indexes can consist of up to 64 columns.
What about the uniqueness of Teradata indexes?
The Teradata primary and secondary indexes can be unique or non-unique. However, the distribution of rows for a non-unique secondary index (NUSI) is different (not distributed according to rowhash).
Can the definition of an index in Teradata be changed?
You can drop a secondary index at any time and create it with a different definition.
We cannot change the column set of a primary index. However, you can change it from NUPI to UPI, for example:
ALTER TABLE Customer MODIFY UNIQUE PRIMARY INDEX (>NUPI columns>)
Does the maintenance of the Teradata Primary Index or Secondary Index cause overhead?
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 we should only create a secondary index if it is also used!
How is a Teradata Index stored?
The primary index does not need its data structure since it simply distributes the rows according to a hashing algorithm.
The secondary index is stored in a sub-table.
What about Row-level Partitioning on Teradata Indexes?
Row-level partitioning is only available for the Teradata primary index.
How are the rows on the AMPs sorted?
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 value (INTEGER datatype only). The unique secondary index can only be sorted by rowhash.