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.

By sorting the rows using the hash value, the AMP can efficiently locate them through a binary search within the data blocks.

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

The Teradata NOPI Table

Column partitioning requires the use of NOPI tables, which allow creating tables without a primary index. This feature was added a few years ago.

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.

Furthermore, since no hash value is available to locate rows within data blocks, a binary search is not feasible. Teradata is therefore forced to read all data blocks.

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.

The Primary AMP Index was created to combine the benefits of the Primary Index — single-AMP access, binary search, and AMP-local joins — with the advantages of the NOPI table, which eliminates the need to sort rows by hash value during loading.

How the Primary AMP Index Works