Understanding Teradata Value Ordering for Improved Range Scans and Data Retrieval

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.

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

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.

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.