DWHPro Insights

Teradata Data Access: Single-AMP vs. All-AMP Binary Search and Sequential Search

By Roland Wenzlofsky ·

Teradata Data Access: Single-AMP vs. All-AMP Binary Search and Sequential Search

Teradata data access is categorized based on the number of AMPs involved and the technique employed to search rows within a data block.

In our article about SQL Tuning, we mentioned that one of the main goals in performance optimization is to distribute the workload evenly across all AMPs.

Using a single AMP is best when retrieving only a few rows. However, when retrieving many rows, all-AMP access is typically the optimal method.

Learn advanced applications for single-AMP access with our article on Tactical Workload Tuning.

This article will explore the various combinations of AMPs and search algorithms within data blocks and their specific applications within Teradata.

Single AMP – Binary Search

The Primary Index is the key representative for a Single-AMP Binary search.

A Primary Index query necessitates one AMP to fetch the eligible row/s from one or multiple data blocks via a binary search.

A simple UPI equality lookup may avoid spool; the complete query can still spool. USI lookup can involve another AMP for the base row and is not identical to UPI access.

Primary Index access is the most cost-effective method to locate disk rows.

Primary Index queries necessitate calculating the Rowhash of qualifying rows from the identical columns as the Primary Index Rowhash of the accessed table.

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 Row Hash is calculated by passing the column values into the hash function if the WHERE clause includes all Primary Index columns.

The Row Hash retrieves rows from their respective AMPs. It is a single-AMP operation since all rows with the same Row Hash are on the same AMP.

Here is an example:

Assuming the Customer Table’s Primary Index comprises the First and Last name fields, we aim to extract data pertaining to “Marianela Garcia”.

"SELECT * FROM Customer WHERE Firstname = 'Marianela' and Lastname = 'Garcia'"

The row hash is calculated, and the information for “Marianela Garcia” is retrieved from the owning AMP due to the presence of all primary index columns in the WHERE clause.

A missing complete PI equality predicate prevents that direct PI lookup, but other indexes, partition elimination or sparse-map access can still narrow work.

"SELECT * FROM Customer WHERE Firstname = 'Marianela'"

The WHERE clause can include all Primary Index and other columns.

The Parsing Engine calculates the Row Hash on the Primary Index to pinpoint the responsible AMP and then filters on the non-Primary Index columns of the WHERE clause to identify qualifying rows.

"SELECT * FROM Customer WHERE Firstname = 'Marianela' AND Lastname = 'Garcia' AND Age = 28"

A maximum of 64 columns can be designated as the Primary Index. However, it is important to note that as more columns are included, the effectiveness of the Primary Index diminishes. This is because Primary Index access necessitates using all columns in the WHERE clause.

An expanding number of Primary Index columns diminishes usability.

Suppose we generate two identical tables, each with a distinct Primary Index.

Table one’s Primary Index is column A, while Table two’s Primary Index is a combination of columns A, B, and C.

(1) PRIMARY INDEX (A);
(2) PRIMARY INDEX (A,B,C);

The Primary Index of Example 2 allows single-AMP access only for the third WHERE clause, while the Primary Index of Example 1 can be used for single-AMP access to all three WHERE clauses.

WHERE A=1
WHERE A=1 AND B=1
WHERE A=1 AND B=2 AND C=10

The Unique Secondary Index (USI) shares the same characteristics as previously described.

All AMP – Sequential Search (Full Table Scan)

A full table scan requires every AMP to perform a sequential search of all data blocks in order to find rows that meet the criteria.

Scan spool size depends on selected columns, filters, aggregation and downstream use, not just the size of the base table.

A full table scan is necessary when the RowHash of the WHERE clause does not correspond to the Primary Index columns of the table being searched, and there are no other access paths, such as a secondary index, to utilize.

The full table scan, while requiring access to every data block of the table, can still be a favorable option. This is because all AMPs begin and end their tasks concurrently, resulting in optimal parallelism.

Base tables, non-unique secondary indexes, and join indexes can undergo full table scans.

All AMP – Binary Search

Teradata uses the All-AMP Binary search for the nonunique secondary index (NUSI).

Teradata keeps the NUSI index rows with the corresponding base table row rather than distributing them by RowHash. As a result, all AMPs must participate in the search for qualifying rows.

RowHash sorts NUSI index rows, enabling binary search for row location.

Tactical Workload Tuning on Teradata
The Primary Index Choice
NUSI – The Nonunique Secondary Index in Teradata
Dramatically improve Performance with the Teradata Nested Join

Hash ordering supports row lookup, but should not be confused with a promise that every physical layout is a simple globally sorted array. Use the access steps and map scope shown in EXPLAIN.

Deciding what to change in your data platform?

Compare optimisation, modernisation and migration against your workload and business requirements.

Explore independent platform assessments

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