Re: Cross join V/s Product Join

CROSS JOIN is a type of joins and product join is a join plan chosen by the optimizer. In product join, total comparisions are multiplication of qualified rows from both the participating tables.Removal of a cross join from your query quite often improves the performance. But question is , can you avoid that join? Sometimes requirement and deign is such that it is almost unavoidable. In such situation , you must keep at least one of the table as small as possible ( or use where clause to keep the dataset as small as possible). In a product join, all rows of one dataset (table) must be compared to the other and to do that optimizer duplicates the small dataset (table) in all the AMPs. If the entire small table cannot fit into the memory, the blocks will have to be read more than once. This is pretty costly.

Re: Cross join V/s Product Join

Teradata tends to make a distinction between a join TYPE (INNER OUTER FULL OUTER CROSS) vs a join STRATEGY (MERGE HASH PRODUCT).

In this case CROSS & CARTESIAN are TYPEs and PRODUCT is the Strategy. Product joins can be chosen as within a PLAN's join GEOGRAPHY to include a product join at any suitable time, even on an INNER join.

EG: a large fact table with many dimentions - the optimiser might first product join all the dimention together and then INNER join the result back to the fact table. I've seen this happen many times.