DWHPro Insights

Teradata Hash Index: Minimizing Disk IOs and Optimizing Row Access

By Roland Wenzlofsky ·

Teradata Hash Index: Minimizing Disk IOs and Optimizing Row Access

The Teradata Hash Index aims to reduce disk IOs by providing an alternative access path to rows, as with all Teradata index structures.

This can be achieved through three primary mechanisms:

  1. The storage of a vertical subset (columns) of the table in the hash index structure
  2. Selecting a better Primary Index to avoid costly data redistribution during join preparation
  3. The Ordering of rows by value to support range scans

The Teradata Hash Index can cover either all or a portion of the selected columns.

If the Teradata Hash Index does not cover the selected columns, the corresponding base table rows can be retrieved in a subsequent step. The ROWIDs, which point to the base table rows, are carried by each index row. The ROWIDs extracted from the Hash Index are placed into a spool and then utilized to access the base table rows. Base table access can be skipped in the first case.

These characteristics are similar to those of Teradata Secondary Indexes. However, Teradata Hash Indexes allow the creation of a custom Primary Index, enabling row distribution among AMPs to be tailored to workload requirements. Secondary Indexes do not offer this flexibility.

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

Teradata Hash Index Design

A Teradata hash index can store rows on the same AMP as the base table row through its Primary Index, which is identical to the Primary Index of the hash index. This is akin to a Non-Unique Secondary Index (NUSI).

The system automatically maintains the Teradata Hash Indexes, as with any index. However, updating, inserting, or deleting data incurs overhead, as the base table and index structures require maintenance.

Accurate statistics are essential for the Primary Index columns of Hash Indexing to enable the Teradata Optimizer to estimate Hash Index usage costs accurately.

When deciding between a Single Table Join Index and a Hash Index, note that although the Hash Index can be seen as a variant of the STJI, there are distinct differences.

The most significant constraints when compared to a Single Table Join Index include:

  • A Hash Index cannot have a Partitioned Primary Index
  • A Hash Index cannot have a Non-Unique Secondary Index.
  • Hash Indexes cannot be specified for NOPI or column‑partitioned base tables as they are designed around the Teradata hashing algorithm.
  • A hash index cannot be column partitioned
  • Hash-index and join-index definition restrictions differ. Column-partitioned tables do not universally lack a PI; check the documented PI, PA and NoPI configurations for the release.

This is an example of creating a Hash Ordered Index:

CREATE HASH INDEX HASH_IDX (COL1, COL2) ON MYTABLE
BY (COL1) ORDER BY HASH (COL1);
DROP HASH INDEX HASH_IDX;
CREATE HASH INDEX HASH_IDX (COL1, COL2) ON MYTABLE
BY (COL1) ORDER BY VALUES (COL2);

The “by” clause defines the columns utilized for data distribution, similar to the Primary Index. However, it is important to note that the columns used for data distribution must be included in the Hash Index column list. To improve fault tolerance, we can utilize FALLBACK protection in the same manner as it is used for base tables.

The Hash Index row’s second copy will be saved on a backup AMP. Without fallback protection, Hash Index use and updates on the base table are impossible when the primary AMP is unavailable.

However, it is important to note that implementing fallback protection requires twice the storage capacity for the Hash Index structure.

Hash indexes and single-table join indexes can offer related access alternatives, but their restrictions and costs differ. The corrected hash/value ordering definitions executed in the audit; no general performance equivalence was established.

Please refer to the official Teradata documentation by following this link:

The Teradata Hash Index

More on indexing here: 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