DWHPro Insights

Teradata Primary vs Secondary Index: Differences and When to Use Each

By Roland Wenzlofsky ·

Teradata Primary vs Secondary Index: Differences and When to Use Each

The PI determines row distribution and a direct access path, but does not guarantee evenness. A business primary key and a PI serve different purposes.

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?

Dropping an index may remove integrity enforcement or affect dependencies; check constraint-backed indexes and workload impact first.
Primary-index changes have release, table and operation restrictions; verify the supported ALTER form for the exact table. 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?

PI choice affects distribution and access; PI-value updates and maintaining row ordering can have costs even without skew.

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 partitioning is also available for supported NoPI tables; it is not limited to PI tables.

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 (supported value-ordered types, including DATE). The unique secondary index can only be sorted by rowhash.

Teradata Docs: The Primary Index

Need help with query performance or platform costs?

Diagnose plans, skew, spool pressure and expensive workloads. Agree a baseline and acceptance criteria before making changes.

Explore Teradata performance tuning

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Follow DWHPro in Google to see our articles more often in Search.

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Protected by Security by CleanTalk and CleanTalk Anti-Spam