DWHPro Insights

Teradata Access Paths: 8 Essential Ones to Know

By Roland Wenzlofsky ·

Teradata Access Paths: 8 Essential Ones to Know

Hash indexes remain supported: creation executed in the isolated fixture. Single-table join indexes offer overlapping and broader capabilities, but have not made hash indexes nonexistent.

1. FULL TABLE SCAN

This technique enables scanning each row only once and grants access to any combination of columns as specified in the WHERE clause. It is the most suitable approach when all rows need to be read without exception.

Scan spool size depends on filtering, projection, aggregation and downstream steps. It need not equal or exceed base-table size.

2. UNIQUE PRIMARY INDEX (UPI)

Simple unique-key lookup can avoid a spool, but that depends on the complete query shape. USI access can involve an index-owning AMP and a base-row AMP, which may coincide; exact AMP counts are not universal for the whole request.

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

None.

3. NON-UNIQUE PRIMARY INDEX (NUPI)

It offers efficient access using only one AMP and enables row-hash locking based on the number of rows selected, which may necessitate a spool file.

NUPI’s SET tables may suffer from inadequate INSERT performance due to duplicate row checks.

4. UNIQUE SECONDARY INDEX (USI)

Index maintenance causes extra overhead for INSERT, UPDATE, MERGE, and DELETE actions.

5. NON-UNIQUE SECONDARY INDEX (NUSI)

NUSI usefulness depends on selectivity, coverage, row/block distribution and other predicates, not simply low distinctness. Compare its cost with the base-table access plan.

It incurs overhead for INSERT, UPDATE, MERGE, and DELETE statements. However, the optimizer avoids using it when accessing many data blocks, since it requires accessing all AMPs. In such cases, a full table scan is often more cost-effective.

6. SINGLE-TABLE JOIN INDEX

Limiting index columns to frequently used ones reduces IOs. The primary index in the index table may differ from the base table.

It incurs overhead for INSERT, UPDATE, MERGE, and DELETE queries.

7. MULTI-TABLE JOIN INDEX

This obviates the need for performing joins or aggregates and can potentially fulfill intricate queries without referencing the source table. Additionally, it can have a unique PI compared to the source table and act as a replacement for NUSI and USI.

Multi-Table Join Indexes result in additional workload for INSERT, UPDATE, MERGE, and DELETE statements that involve any associated base tables. It is recommended to avoid utilizing them for tables that undergo substantial modifications.

8. SPARSE JOIN INDEX

The overhead for statements such as INSERT, UPDATE, MERGE, and DELETE is minimized by limiting the storage of index rows compared to a standard join index. Furthermore, the optimizer can utilize it to access infrequent values, given that it is restricted to selective column values.

Causes overhead for INSERT, UPDATE, MERGE, and DELETE statements.

The Teradata Join Index

​The Teradata Primary Index

​The Teradata NUSI

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