title: When does the Oracle CBO choose to execute a "merge join cartesian" operation?
body: <p>From time to time, Oracle seems to prefer a <code>MERGE JOIN CARTESIAN</code> operation over a regular <code>MERGE JOIN</code>. Knowing the data and looking at concrete execution plans, I can see that this operation is usually not a problem, as one of the joined entities can return only exactly one record in the query at hand.</p>
  
  <p>However, for historic reasons, our DBAs have a general distaste for cartesian products.</p>
  
  <p>So I'd like to better analyse those cases and be backed up with documentation in my argumentation. Is there any official Oracle documentation about query transformation and the CBO where I can understand the cases when Oracle prefers the <code>MERGE JOIN CARTESIAN</code> (or similar) operation?</p>
  
  <p>In this case, I'm Using Oracle 11g (11.2.0.2.0)</p>
  
  <p><strong>UPDATE</strong>:</p>
  
  <p>These are similar questions, but they don't explain <em>why</em> or <em>when</em> Oracle prefers the <code>MJC</code> over a regular <code>MERGE JOIN</code>:</p>
  
  <ul>
  <li><a href="https://stackoverflow.com/questions/3459066/why-does-this-query-result-in-a-merge-join-cartesian-in-oracle">Why does this query result in a MERGE JOIN CARTESIAN in Oracle?</a></li>
  <li><a href="https://stackoverflow.com/questions/3070120/why-would-this-query-cause-a-merge-cartesian-join-in-oracle">Why would this query cause a Merge Cartesian Join in Oracle</a></li>
  </ul>