Understanding Teradata Value Ordering for Improved Range Scans and Data Retrieval
Learn about Teradata value ordering, an optimization technique for range scans of 32-bit integers. Read on to understand how it is implemented.
Numerous online articles discuss Teradata Hashing, which evenly distributes physical data rows across all AMPs and sorts them based on ROWID.
Limited information is available regarding value ordering. The official Teradata documentation vaguely states that it allows for optimized range scans of values. Technical descriptions typically conclude with an example of using WHERE conditions on different dates, which is insufficient for a thorough understanding of this topic.
It is time to clarify value ordering.
Want more practical data engineering analysis like this?
Join DWHPro Letters and get field-tested notes on Teradata, Snowflake, AI, migrations, performance, and enterprise data work. DWHPro Letters is free. Subscribe to get new issues by email.
In principle, we have two possibilities for using value ordering:
In the past, only the non-distinct secondary index permitted value ordering. However, Teradata 12's release made value ordering available for join indexes.
How Teradata Value Ordering is implemented
Get the next issue by email.
If you've been following our physical design posts, you already understand the process of data retrieval on a Teradata system.
- The Parsing Engine calculates a hash value (the ROWHASH) over the rows' Primary Index column(s).
- The so-called hashmap is consulted to identify the responsible AMP for each row.
- The AMP checks its Master Index to determine the cylinder carrying the row(s).
- The AMP checks the Cylinder Index to determine the disk sector(s) containing the row(s) (a sequence of sectors that makes up a data block).
Primary Index hashing utilizes the Master and Cylinder Index to locate the ROWHASH. These indices provide information on the cylinder and sectors where the rows are stored. Additionally, ROWID-sorted row pointers are maintained within data blocks.
Value ordering is the process by which the Master Index and Cylinder Index contain information about the location of a 32-bit value (as opposed to the ROWHASH). In addition, the data block has a row pointer array sorted based on this 32-bit value.
The "date" data type, which is stored as a 32-bit integer, is the only acceptable format for value ordering. This is because the basic hashing technique relies on the same physical structures (such as the Master Index and Cylinder Index dictionaries and the row pointer array) and has not undergone any design changes.
A few critical remarks are worth noting. First, value ordering is exclusively an All-AMP operation. Second, while the Join Index permits the definition of any Primary Index, it is solely used for data distribution. Third, the NUSI option is inherently designed to require a complete table scan.
A Join Index's value ordering offers the same benefits as a single-partition PPI table while using less space.
Indexing for value ordering requires All-AMP access and a subsequent search of a 32-bit value.
The strength of the value ordering method is determined by the sequence of the 32-bit integer column in each physical structure, namely the Master Index, Cylinder Index, and Data Block.
Learn more about indexing by visiting Teradata Indexing.
Planning or surviving an enterprise data platform migration?
I write regularly about the performance, cost, architecture, and project mistakes that show up in real Teradata, Snowflake, Databricks, and enterprise data work.
Subscribe for free and keep launch access.
Written by Roland Wenzlofsky, founder of DWHPro and author of Teradata Query Performance Tuning. DWHPro has helped data warehouse practitioners for 15+ years.