DWHPro Insights

Understanding Teradata’s Primary AMP Index for Improved Performance

By Roland Wenzlofsky ·

Understanding Teradata’s Primary AMP Index for Improved Performance

Experienced Teradata users are familiar with the concept of a primary index. However, a new term has surfaced with the introduction of Teradata Release 15: Primary AMP Index.

This blog post will demonstrate a Primary AMP Index’s benefits and optimal usage for improving load and query performance.

First, let us examine Teradata’s approaches over the years to distributing data evenly among all AMPs.

The Teradata Primary Index

Initially, only the Primary Index existed. It defines the table’s columns that generate a hash value, which determines which AMP manages each row.

The hash value is used to sort the rows of primary index tables.

Using the Primary Index and sorting by hash value enables the Optimizer to restrict the row search to the corresponding AMPs.

Column-partitioned tables can use a primary index, primary AMP index or no primary index. A primary AMP index controls placement without PI-style rowhash sorting and binary-search lookup.

Teradata can join two AMP-local tables without redistributing rows if they have the same primary index.

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

The Teradata NOPI Table

NOPI tables are distributed evenly and randomly across all AMPs.

To locate rows, one must search through all AMPs, as no specific hash values are assigned to them.

NOPI tables are commonly used in staging, since bulk load utilities such as Fastload distribute rows uniformly across all AMPs using a round-robin method, eliminating the need to sort them by hash value.

Before Teradata Release 15, column partitioning was exclusively available through this method.

The sole benefit of column partitioning in this case is limiting I/Os to only the necessary columns, since a full table scan is always required.

Joining a NOPI table requires row redistribution.

The Teradata Primary AMP Index

The Primary AMP Index was developed to address the limitations identified with NOPI tables.

Skewed tables are uncommon for NOPI tables, although they can occur when transferring data from a skewed table via an INSERT…SELECT command, which inserts rows into the local AMP of the NOPI table.

To address this issue, a new syntax was introduced to enable row redistribution based on a hash value.

Since Teradata 15, columnar tables can have a primary index, making NOPI tables less significant.

How the Primary AMP Index Works

How are the Rows of the Primary AMP Index distributed?

As with the Primary Index, the AMP responsible for a row is determined by hashing.

How is the data of the Primary AMP Index stored on the AMPs?

The data is not sorted. This means that a binary search in the data blocks is not possible. The AMP must search all data blocks.

When can the Primary AMP Index be used?

We can only use the Primary AMP Index for tables partitioned by columns.

What is the advantage of the Primary AMP Index over a Primary Index?

A primary AMP index can reduce loading sort work and support compatible AMP-local joins, but performance depends on maps, hash compatibility and workload. NoPI plans can use partition/container elimination and may duplicate the other input; neither design guarantees the fastest load or join.

What is the advantage of the Primary AMP Index compared to a NOPI table?

A primary AMP index can reduce loading sort work and support compatible AMP-local joins, but performance depends on maps, hash compatibility and workload. NoPI plans can use partition/container elimination and may duplicate the other input; neither design guarantees the fastest load or join.

What should I use for Column Partitioned Tables? PI, NOPI, or Primary AMP Index?

I would start with Primary AMP Index Tables because they have advantages for loading and executing queries (Join performance).
But as always, this design decision can vary depending on the application.

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.

2 thoughts on “Understanding Teradata’s Primary AMP Index for Improved Performance”

  1. Could you elaborate on the disadvantages of Primary Amp?
    when it should NOT be used, except for some tactical queries maybe (because all the data blocks of the AMP must be searched)?

    Reply

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