Teradata uses different join methods and has different ways to bring the rows of the two tables to a common AMP, which is a prerequisite for joining. Join geography and join method are called join strategy; for both, the goal is to minimize the resource consumption (IOs, CPU seconds). There are join methods that are more efficient in terms of the algorithm, such as the Teradata Merge Join, but may have high costs for preparation (e.g., the merge join if the two tables have different primary indexes or partitioning), and there are join methods that are not so efficient but have no costs for the join preparation (join geography).
We should consider the following costs in the Join strategy:
- The number of required comparisons between the rows of both tables.
- How often must each data block of both tables be loaded from the disk into the main memory during the join process?
- The amount of data that must be copied either by duplicating or redistributing between AMPs.
- The number of rows of both tables that need to be sorted (if any).
The merge join is very efficient and requires few comparisons once the rows are on the common AMP and sorted. This is the case if both the primary index and the partitions of both tables are identical. Then these steps are not needed at all. Otherwise, it may be necessary to redistribute and sort one or both tables depending on the situation. In the case of row partitioned tables, it may be required to convert one or both to non-partitioned tables or to partition the non-partitioned tables. All of these preparation steps for the actual join can be very expensive.
On the other hand, the product join is usually less cost-intensive in terms of preparation but expensive in terms of the number of comparisons since each row of one table must be compared with each row of the other table. Typically, the only preparation for a product join is duplicating the smaller of the two tables to all AMPs. Rows do not need to be sorted but can be compared immediately.
The product joins becomes expensive when a large table is mistaken for a small table due to missing or outdated statistics. Then the tiny table may no longer fit entirely into the main memory, and Teradata must read data blocks several times from the disk into the main memory.
Summarizing once again the cost characteristics of the product join:
- High costs for comparison
- Low costs for join preparation
Summarizing once again the cost characteristics of the merge join:
- No costs (if the primary index and partitioning of both tables are the same) to enormous costs for join preparation
- Low costs for comparison
Summarizing once again the cost characteristics of the merge join:
Now let’s move on to our test scenario where I want to show you that the demonized product join doesn’t always have to be wrong. If the execution plan contains a product join, you should pay attention to the following things:
- Is it an unwanted product join, e.g., because of a wrong alias or a forgotten join condition?
- Is at least one of the two tables tiny?
If one of the two tables is small, a product join may perform better than a merge join. Not to forget: Teradata can also apply dynamic partition elimination to product joins.
The Teradata Merge Join
Below you can see a query that is solved by a merge join:
SELECT Customer.ISO, Product.Desc
FROM Customer INNER JOIN Product ON Customer.ProductCd = Product.ProductCd;4)We do an all-AMPs RETRIEVE step from Customer by way of an all-rows scan with no residual conditions into Spool 2, redistributed by hash code to all AMPs. Then, we do a SORT to order Spool 2 by row hash. The result spool file will not be cached in memory. The size of Spool 2 is estimated with low confidence to be 10,000,100 rows. The estimated time for this step is 30 minutes and 12 seconds.
5) We do an all-AMPs JOIN step from Customer by way of a RowHash match scan with no residual conditions, which is joined to Spool 2 (Last Use). Product and Spool 2 are joined using a merge join, with a join condition of ("Product.ProductCd=ProductCd"). The result goes into Spool 1, which is built locally on the AMPs. The result spool file will not be cached in memory. The size of Spool 1 is estimated with low confidence to be 10,000,000 rows. The estimated time for this step is 11 minutes and 20 seconds.As you can see from the Execution Plan, the estimated time for the join preparation is about 30 minutes, and then the join itself is estimated at 11 minutes. These estimates also correspond to the actual runtime of our query. As you can see, all table rows have to be redistributed and sorted so that the primary index of all tables matches or the rows are on a common AMP.
The Teradata Product Join
Finally, we improve the query with a WHERE condition, which causes the optimizer to switch to a product join:
SELECT Customer.ISO, Product.Desc
FROM Customer INNER JOIN Product ON Customer.ProductCd = Product.ProductCd
WHERE Product.Price <= 1000;4) We do an all-AMPs RETRIEVE step from Product by way of an all-rows scan with a condition of ("Product.Price <= 1000") into Spool 2, which is duplicated on all AMPs. The size of Spool 2 is estimated with high confidence to be 100 rows. The estimated time for this step is 0.15 seconds.5) We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of an all-rows scan, which is joined to Customer. Spool 2 and Customer are joined using a product join, with a join condition of ("ProductCd = Customer.ProductCd"). The input table Customer will not be cached in memory, but it is eligible for synchronized scanning. The result goes into Spool 1, which is built locally on the AMPs. The size of Spool 1 is estimated with low confidence to be 800,000 rows. The estimated time for this step is 18 minutes and 17 seconds.The new Execution Plan estimates less than a second for copying the tiny table to all AMPs and about 18 minutes for the join. This again corresponds approximately to the actual runtimes.
The Product join performs significantly better than the Merge Join.
Our example is interesting in that the WHERE condition prepares the way for a product join. Alternatively, the optimizer could copy or distribute the small table to all AMPs, sort it and then join it using a merge join with a rowhash match scan. Apparently, in this case, the additional cost is higher than joining the unsorted table. If Teradata joined both tables via columns that are not the primary index of both tables, the optimizer would even have to sort both tables, which would be very expensive in the case of a large table.
Summary
Although it is common for many people to be horrified when they see a product join, it is not necessarily bad. First, we must not forget that the product join is the only join method that works for all types of joins. Sometimes the optimizer has no choice but to perform a product join (for example: OR conditions in the join)
Since you are interested in join methods and strategies, I can also recommend this article to you:
If you want to know more about product joins, we can also recommend the official Teradata documentation:
Thank you very much, Aleksei.
This was an excellent hint: Sometimes, caused by biased data demographics, a product join is hidden behind a merge join. This was a great example.
hi sir, can you please let me know the best way to self join same table multiple times based on different filter in the join clause. Ex: joining to country table multiple times on country_id (just for example sake provided)
on 1st two joins doing “We do an All-AMPs JOIN step from Spool 434 by way of an all-rows scan, which is joined to table abc. Spool xyz and table abc are left outer joined using a merge join . The result goes into Spool xyz1, which is redistributed by hash code to all AMPs. This step ends a parallel block of steps.”
but
its just doing “We do an All-AMPs RETRIEVE step from table abc by way of an all-rows scan into Spool 123, which is duplicated on all AMPs.” on the 3rd join and this duplication is taking long time
Hello.
Thank you for the article.
I have some small addition: a merge join may contain a product join inside.
Let’s assume that we have the same non-UniquePrimary Index( Store_id and good_id) for 2 different tables.
Each good may be sold many times in some stores. Let’s assume that it is sold 1000 times in some store but on different days(which is not a part of PI). All 1000 records will have the same rowhash, stored on the same APM, and will have a different uniques value in its rowid.
If we try to “make” a merge join of two tables then Teradata do real merge join algorithm between different row hashes(for example if some store is presented in one table but does not present in second then the rowhashes is not matched and that store will be skipped) but it has to make the product join between rows shared the same rowhash value(so for some particular pair of Store_id and Good_id all rows of the first table will be product joined to the corresponding rows from the second table).
This is the reason why the performance of merge join may be decreased in some cases.
Another popular reason is a different partitioning between joined tables but it not a theme of this article.
Best regards,
Aleksei Svitin.