Merge Join Cartesian is very expensive to Oracle. It is never useful except for the case when two tables have many to many join relation and they have very few rows. In general, Merge Join Cartesian tells us that there is something wrong with the query. Below given are some tips to troubleshoot and remove the deadly Merge Join Cartesian:
1. Check Join Conditions
Start with the table joins. Check if there are any join conditions missing between the given tables in the query. As I said, Cartesian product is only possible when two tables have Many to Many join relation. In normal scenarios, the joins should be of the type: (a.primary_key = b.foreign_key)
In most of the cases, adding proper join conditions will remove the Merge Join Cartesian from the plan.
2. Stale Statistics
The optimizer selects the best execution plan on the basis of the object statistics. If the statistics are stale, the plan might not be the optimal plan. Gather the statistics using DBMS_STATS package.
3. Optimizer Bug (_optimizer_transitivity_retain)
The issue with Optimizer regarding (_optimizer_transitivity_retain) is explained best by Oracle ACE Syed Jaffer:
Conclusion:
If the database design is correct and if proper join conditions are used, then there are very little chances that the Optimizer will use Merge Join Cartesian in the for the execution.
I was looking for this problem. Sometimes our database uses cartesian joins for the queries which never completes (or takes very very long time).
We have analyzed the tables, but still getting problems.
Any help appreciated !
Amit, sorry for late reply.
You should check your query and see if you have enough join conditions between the tables.
Ideally, the PK of a table should be mapped with FK in the other table.
This will make sure that your query itself if not producing cartesian product between two tables.