Optimizing Teradata Joins: Tips to Reduce Resource Consumption
Joining tables can be costly since each join requires that the rows being joined are on the same AMP. In cases where the two tables possess different primary indices, copying one or both tables' rows may be necessary, which can cause a high number of I/Os in large tables. The Teradata Optimizer can select from various join methods for each join, tailored for a specific scenario. The primary objective of the Teradata Optimizer is to minimize resource consumption, including I/Os, CPU seconds, network traffic, and other factors, for the entire execution plan.
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.
General Teradata Join Optimization Considerations
The optimizer utilizes statistics to determine the optimal join plan and estimates the cost of each join based on the following factors:
- The expected cardinality of the output spool (after the join) and
- The expected cardinality of the input spools after WHERE conditions have been applied.
- The selected column lists each input spool and the average row size. Usually, only the selected columns of each input spool are taken over into the resulting spool.
The only exception is if a permanent table is joined directly without spooling. In such a case, all columns of this table are copied into the resulting spool.
Based on the above observations, we can draw the following conclusions regarding performance:
The essential statistics for each join are the ones on the join columns and any available WHERE condition.
We should apply WHERE conditions in our queries whenever possible to reduce the input tables' cardinality.
Further, we should only select the columns required in the result set and avoid „SELECT * FROM“ queries.
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.