Teradata Access Paths: 8 Essential Ones to Know

The optimizer has various access paths to consider when creating the execution plan, each with unique advantages and disadvantages. This article will introduce the 8 most essential access paths. The lack of a hash index may surprise you; however, Teradata has entirely substituted it with the single-table join index and discourages its utilization in the newest documentation. The single-table join index can execute all the hash index functions and more.

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.

The program reads each data block from a table and usually requires a spool table that is equal to or greater in size than the original table.

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.

Get the next issue

2. UNIQUE PRIMARY INDEX (UPI)

The most economical way to access data is by utilizing the primary index columns in the WHERE condition. This method exclusively accesses a single AMP and does not produce a spool file. It uses row hash locking, which provides a highly precise approach.

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)

This method optimally retrieves queries with a WHERE condition, including all USI columns, using only two AMPs, eliminating the need for a spool table.

If you work with enterprise data platforms, migrations, performance tuning, or AI-driven delivery teams, DWHPro Letters is written for you. Get the next issue by email.

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

5. NON-UNIQUE SECONDARY INDEX (NUSI)

It facilitates efficient access for columns with low distinct values, but requires all AMPs to be accessed, which may result in a spool table.

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.

​

​

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.