Product joins, cross joins, or Cartesian joins, are mainly created unintentionally. These are the most expensive types of joins when you are joining two tables with millions of rows.
In the cross join, every column in the left table is joined to every column in the right table! So, if you have a one billion row table and cross join it to a 100 row table, your answer set will have 100 billion rows!
These joins happen when you:
- Mention
CROSSjoin explicitly - Missed a join condition in a query as shown in following code
- Wrongly used aliases in a query
/*Missed join PRODUCT JOIN*/ SELECT COUNTRY_ID, REVI_ID, AUCT_CODE, FROM COUNTRIES, -- NO JOIN SPECIFIED AUCT_TYPES
And when you check the EXPLAIN plan for the query, you will get the product join in it:
/*EXPLAIN PLAN SHOWING PRODUCT JOIN*/ 4) We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of an all-rows scan, which is joined to Spool 7 (Last Use) by way of an all-rows scan. Spool 2 and Spool 7 are joined using...