Product Join | Join Planning/Optimization | Teradata Vantage - Product Join - Advanced SQL Engine - Teradata Database

Definition of the Product Join

The product join compares every qualifying row from one relation to every qualifying row from the other relation and saves the rows that match the WHERE predicate filter. Because all rows of the left relation in the join must be compared with all rows of the right relation, the system always duplicates the smaller relation on all AMPs, and if the entire spool does not fit into available memory, the system is required to read the same data blocks more than one time. Reading the same data block multiple times is a costly operation.

This operation is called a product join because the number of comparisons needed is the algebraic product of the number of qualifying rows in the two relations.

Product Join Types

Processing a Product Join

The following list outlines the product join process:

1. Cache the left relation rows.
2. Join each row of the right relation with each row from the cached left relation.

An overall join condition is a WHERE constraint that links relations to be joined on a column that is common to each, as shown in the following example:

WHERE employee.deptno=department.deptno

The following graphic illustrates the generic product join process.

The following graphic illustrates a product join where rows from the Project table are duplicated to all AMPs.

Product Join Costs

Product joins are relatively more time consuming than other types of joins because of the number of comparisons that must be made.

The product join is usually the most costly join method available in terms of system resources, and is used only when there is not a more efficient method, such as a merge join, hash join, or a nested join. However, a product join is useful because it can resolve any combination of join conditions.

Product Joins With Dynamic Row Partition Elimination

The database performs dynamic row partition elimination, or DPE, for product joins in the AMPs after a query has already been optimized, so in this case, the row partition elimination undertaken is dependent on the actual data on disk, not a statistical estimate of that data.

The term direct product join describes a join in which the table or join index of interest is not spooled in preparation for a product join, but is instead joined directly. The Optimizer might choose a direct product join when all the partitioning columns of one or more partitioning expressions are specified in equality join terms.

As is the case for static partition row elimination (see Static Row Partition Elimination), the database applies the product join DPE optimization for single-level partitioning independently for each partitioning expression.

Row partition elimination methods can be mixed within the same query. For example, static row partition elimination can be used for some partitioning levels, while DPE can be used for other levels, and some levels might not evoke any partition elimination. Some partition levels might even benefit from multiple forms of partition elimination.

The system combines the result of the partition elimination process to determine which internal partitions need to be accessed. Combining can occur for Static Partition Elimination as part of generating the query plan.

DPE occurs within the AMPs as row sets are processed, and is combined with any static row partition elimination from the query plan at that point.

When performing join steps, the qualifying row partitions of the row-partitioned table or join index are determined dynamically based on the values of rows selected from the other relation in the join. Instead of a product join against all the rows in the row-partitioned table, the database does a product join only for each set of rows of the other relation of a binary join that match with a single row partition. The database does not necessarily choose to use this optimization for this type of query because other join plans might have a better estimated cost depending on the demographics of the data and the form of partitioning that is used.

DPE for product joins can occur when there is an equality constraint between a partitioning column of one table and a column of another relation in the join. This is useful when the system uses a product join and must look up rows in one table and match those rows to rows in corresponding row partitions instead of performing a product join to the entire table. In this case, only those row partitions that are required to answer the request take part in the join.

For a product join with DPE, the left relation is sorted by RowKey using the same partitioning expression as the right relation.

Rows from the left relation are loaded into the cache one row partition at a time. This partition is then set as the partition for reading the right row-partitioned table. When the end of the partition for the right table is reached, the system reloads the left cache with the next row partition, and the process is repeated.

For example, if there are 100 row partitions in the row-partitioned table and only 5 of them are needed to answer the join request, the system does not join the other 95 row partitions, providing a 95% resource saving for the operation.

Product Join With Dynamic Row Partition Elimination for Character Partitioning

For the Optimizer to specify a product join with DPE for a given row partitioning level, the collation for the current session need not match the collation for the row-partitioned table when all equality join terms on character partitioning columns are case-sensitive.