DWHPro Insights

The Teradata NESTED join and the impact of the Primary Index

By Roland Wenzlofsky ·

The Teradata NESTED join and the impact of the Primary Index

The MERGE join is the most commonly utilized join strategy, or its equivalent for row partitioned tables, known as the row key-based merge join. One seldom-seen join strategy is the Teradata NESTED join.

The Teradata Optimizer must carefully consider query design to select the most cost-effective joining method.

First, we will examine the mechanics of a nested join and the technology that facilitates it.

Nested access commonly combines a selective unique lookup with a lookup of the other input. Eligibility depends on the query, indexes and supported optimizer transformations.

Inspect EXPLAIN for the actual access paths instead of assuming every primary or secondary index combination guarantees a nested join.

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

In a fast-paced setting, where avoiding unique primary indexes is common practice to conceal duplicate key issues, numerous opportunities to perform a nested join go unnoticed.

This historical scenario illustrates nested access. Its original data setup did not support the reported before/after performance comparison, so the I/O numbers below are retained only as historical observations.

I created two test tables, allowing the NESTED join strategy to be used.
CREATE MULTISET TABLE TABLE_1 (PK INTEGER NOT NULL, ATTRIB_A  INTEGER) UNIQUE PRIMARY INDEX (PK);
CREATE MULTISET TABLE TABLE_2 (PK INTEGER NOT NULL, ATTRIB_B  INTEGER) PRIMARY INDEX (ATTRIB_B );

I inserted arbitrary data into both tables.

INSERT INTO TABLE_1 SELECT ROW_NUMBER() OVER ( ORDER BY 1), RANDOM(1,100) FROM SYS_CALENDAR.CALENDAR;
INSERT INTO TABLE_2 SELECT ROW_NUMBER() OVER ( ORDER BY 1), ROW_NUMBER() OVER ( ORDER BY 1)  FROM SYS_CALENDAR.CALENDAR;

The following filters return no matching rows for the shown TABLE_2 population: its PK and ATTRIB_B are generated identically, while the query requests 1 and 10. Use matched test data before drawing performance conclusions.

SELECT * FROM TABLE_1 t01 INNER JOIN TABLE_2 t02 ON  t01.PK = T02.PK
WHERE t01.PK = 1 AND t02.ATTRIB_B = 10;

The original report recorded two disk accesses, but this is not a reproduced measurement of a nonempty join result.

Changing a UPI to a NUPI can change optimizer choices. The DROP/recreate sequence below leaves TABLE_1 empty unless its data is reloaded; do not compare it with the populated original. Nested-join eligibility also depends on the complete query and available unique access paths.

DROP TABLE TABLE_1;
CREATE MULTISET TABLE TABLE_1 (PK INTEGER NOT NULL, ATTRIB_A  INTEGER) PRIMARY INDEX (PK);

The historical plan reportedly used a merge join; a current plan must be checked after restoring identical input rows and statistics.

The historical 228-versus-2 I/O comparison is not controlled by the published script and does not establish a performance penalty.

Conclusion: 

Do not disregard the uniqueness of primary indexes for dubious reasons.

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.

3 thoughts on “The Teradata NESTED join and the impact of the Primary Index”

  1. Hi Roland,
    Thanks for another great post.

    Do you know the exact reason why 2 disks are accessed in the first case and 228 in second?
    How the process works in 2 disk scenarios and different from 28 one.

    Reply
  2. Hi Roland,

    How to find out the number of disk accesses or I/Os done in satisfying a particular query?

    Regards,
    Virendra

    Reply

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