DWHPro Insights

Teradata Row Retrieval: Understanding Hashing, Indexing, and Search Algorithms

By Roland Wenzlofsky ·

Teradata Row Retrieval: Understanding Hashing, Indexing, and Search Algorithms

Introduction

Teradata uses various mechanisms, such as hash maps, master and cylinder indexes, and binary and sequential search algorithms, to locate table rows. This article explains the process of locating table rows in Teradata using these elements and techniques.

Hash Maps

Teradata’s architecture utilizes the Massively Parallel Processing (MPP) model, which distributes data among Access Module Processors (AMPs). To distribute and retrieve data, Teradata employs a hashing algorithm. The primary index (PI) value is hashed to produce a row hash value during a table row search. This value is used to locate the corresponding AMP in a hash map, ensuring the efficiency and speed of data retrieval operations.

Master Index

For the traditional row-store access path described here, the master index leads to the relevant cylinder-index structures, which locate data blocks. It is not a direct map from every row hash to every data block.

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

Cylinder Index

Teradata’s disks are divided into cylinders, each with its own Index that maps row hash values to data block locations. This allows Teradata to efficiently locate the data block containing the searched row.

Binary and Sequential Search within Data Blocks

Within a row-store block, the row references and row identifiers support locating matching rows. Exact search details depend on format and access path; the following binary and sequential descriptions are conceptual, not a guaranteed size-based choice in every release.

  • Binary Search: an ordered row-reference structure can narrow a search by row identifier or row hash. Hash collisions require comparing the actual key values; partitioned row-store ordering also includes partition information.
  • Sequential Search: scans inspect rows or ranges when the chosen access path calls for them. The choice is not established solely by a block-size threshold in this article.
  1. Rows can be found through PI hashing, secondary-index row references or scans. A scan does not begin with a known target RowID. Table identity, storage format and comparison predicates determine the lookup work.

Conclusion

Teradata achieves efficient row retrieval using hash maps, master indexes, cylinder indexes, and binary and sequential search algorithms. Understanding these components and their interactions helps database administrators and developers better appreciate Teradata’s performance capabilities and optimize data warehousing operations.

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