6

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.

Mark Sherretta
  • 10,160
  • 4
  • 37
  • 42
  • 5
    Is the merge join cartesian actually causing performance problems or do you just not expect to see it? If one of the tables you are joining to have a small number of rows merge join cartesian can be invoked. Also Oracle Version would be helpful as optimizers are changed/improved very much from release to release. – David Mann Aug 11 '10 at 16:56
  • Yes, the query took over a minute to return with the cartesian join. Once I added the ordered hint, it returned in < 1s. This is Oracle 10g. Trckd_Prod and Prod_Brnd are small tables, the other 3 are very large. – Mark Sherretta Aug 12 '10 at 17:15
  • @MarkSherretta - the 10g optimizer is known to be flaky and to require hints which later versions of the optimizer don't need. 11.1's pretty solid - 11.2 better. I don't have any personal experience with 12 so can't say how it's doing. – Bob Jarvis - Слава Україні Jun 15 '16 at 02:42

3 Answers3

3

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.

Mark Sherretta
  • 10,160
  • 4
  • 37
  • 42
  • Using the ordered hint may have solved your performance problem. But, your answer doesn't shed any light on why MERGE CARTESIAN JOIN is being preferred. Can you elaborate further if you have any reference documentation? – Sarath Chandra Nov 19 '18 at 08:56
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.

Allan
  • 17,141
  • 4
  • 52
  • 69
0

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

RMorrisey
  • 7,637
  • 9
  • 53
  • 71