DWHPro Insights

Understanding Teradata Value Ordering for Improved Range Scans and Data Retrieval

By Roland Wenzlofsky ·

Understanding Teradata Value Ordering for Improved Range Scans and Data Retrieval

Primary-index hashing and maps distribute rows to AMPs, but do not guarantee even placement. Ordered row-pointer structures support access independently of physical row arrival order.

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.

Value ordering is available for supported secondary, join and hash index definitions. Their syntax and restrictions differ; consult the relevant index definition for the target release.

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

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

If you’ve been following our physical design posts, you already understand the process of data retrieval on a Teradata system.

  1. The Parsing Engine calculates a hash value (the ROWHASH) over the rows’ Primary Index column(s).
  2. The so-called hashmap is consulted to identify the responsible AMP for each row.
  3. The AMP checks its Master Index to determine the cylinder carrying the row(s).
  4. 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.

DATE is not the only supported type for value ordering. An INTEGER value-ordered NUSI was created successfully in the functional test. Follow the documented type and width restrictions for the index type and release.

Value-ordered NUSI entries remain local to their base rows. Access can span multiple AMPs, but additional predicates and maps affect participation, and NUSI use does not inherently require a full base-table scan. Join-index distribution is a separate design choice.

A value-ordered join index and a row-partitioned base table are different physical designs. Their storage, maintenance and access costs depend on the selected columns, predicates and data; they are not guaranteed to provide identical benefits with less space.

Use EXPLAIN to determine participating AMPs and the range access actually selected. Value ordering does not by itself mandate an all-system-AMP scan.

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.

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