The Teradata primary index differs from a primary key. Its sole purpose is to distribute data evenly among all AMPs. Though the primary index and primary key are frequently equivalent, the primary index is not inherently a primary key.
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 anytime 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?
A rowhash of the primary index of an unpartitioned table sorts the rows. 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.
