Every join in Teradata follows one rule: the rows that belong together must sit on the same AMP. How Teradata gets them there, and how it then matches them, is the join strategy. It decides how much data is copied between AMPs, how much is sorted, and how long your query runs.
Part of the Teradata SQL Tuning and Query Rewriting guide.
In this six-minute video, we explain the four join methods you’ll find in almost every execution plan: merge join, hash join, nested join and product join. You’ll see how the optimizer chooses between redistributing and duplicating rows, why statistics matter, and what to look for in the EXPLAIN.
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
What the video covers
- 0:33 Why matching rows must be on the same AMP
- 1:06 Redistribution or duplication: how rows are brought together
- 1:51 How the optimizer decides
- 2:22 Merge join, the Swiss army knife
- 3:21 Hash join, the sprinter
- 3:53 Nested join, the fastest but rare
- 4:35 Product join, and why it isn’t automatically a mistake
- 5:16 What you can do to help the optimizer
Want the details, including real execution plans for the merge join variants on partitioned tables? Read the full article: Teradata Join Strategies: Merge, Hash, Nested and Product Joins.
Joins slow, or spool running out? Tell us about your workload, and we’ll take a look.
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