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 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 2

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.