December 6

0 comments

Master the Differences between Teradata Primary and Secondary Index

By DWH Pro Admin

December 6, 2019

NUSI, secondary index, USI

Which of them is required?

None of these indices is mandatory. Nevertheless, in most cases, a primary index should be defined.

How many indices can be defined per 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 index and the secondary index can consist of up to 64 columns.

What about uniqueness?

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).

Can the definition of the index be changed?

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>)

How about overhead for index maintenance?

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!

How is a Teradata Index stored?

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.

What about Row-level Partitioning?

Row-level partitioning is only available for the 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 by value (INTEGER datatype only). The unique secondary index can only be sorted by rowhash.

DWH Pro Admin


Teradata and Data Warehouse Specialist with more than 20 years of experience

You might also like

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Never miss a good story!

 Subscribe to our newsletter to keep up with the latest trends!

>