Eliminating product joins

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 CROSS join 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...