Why does this query result in a MERGE JOIN CARTESIAN in Oracle?

Question

Here is my query:

select count(*)
from email_prod_junc j
inner join trckd_prod t5 on j.trckd_prod_sk = t5.trckd_prod_sk
inner join prod_brnd b on t5.prod_brnd_sk = b.prod_brnd_sk
inner join email e on j.email_sk = e.email_sk
inner join dm_geography_sales_pos_uniq u on (u.emp_sk = e.emp_sk and u.prod_brnd_sk = b.prod_brnd_sk)

The explain plan says:

Cartesian Join between DM_GEOGRAPHY_SALES_POS_UNIQ and EMAIL_PROD_JUNC.

I don't understand why because there is a join condition for each table.

Answer 1

I would speculate that it happens because of the on (x and y) condition of the last inner join. Oracle probably doesn't know how to optimize the multi-statement condition, so it does a full join, then filters the result by the condition after the fact. I'm not really familiar with Oracle's explain plan, so I can't say that with authority

Edit

If you wanted to test this hypothesis, you could try changing the query to:

inner join dm_geography_sales_pos_uniq u on u.emp_sk = e.emp_sk 
where u.prod_brnd_sk = b.prod_brnd_sk

and see if that eliminates the full join from the plan

Answer 2

I solved this by adding the ORDERED hint:

select /*+ ordered */

I got the information from here

If you specify the tables in the order you want them joined and use this hint, Oracle won't spend time trying to figure out the optimal join order, it will just join them as they are ordered in the FROM clause.

Answer 3

Without knowing your indexes and the full plan, it's hard to say why this is happening exactly. My best guess is that EMAIL_PROD_JUNC and DM_GEOGRAPHY_SALES_POS_UNIQ are relatively small and that there's an index on TRCKD_PROD(trckd_prod_sk, prod_brnd_sk). If that's the case, then the optimizer may have decided that the Cartesian on the two smaller tables is less expensive than filtering TRCKD_PROD twice.