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.
Part of the Teradata Physical Design and Data Modelling guide.
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.
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 bookIn 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.
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
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.
You can find this information in the DBC.DBQLOGTBL. You will have logical and physical IOs available.
Hi Roland,
How to find out the number of disk accesses or I/Os done in satisfying a particular query?
Regards,
Virendra