title: Oracle how to avoid merge cartesian join in execution plan?
body: <p>The following query is sometimes resulting in a merge cartesian join in the execution plan, we're trying to rewrite the query (in the simplest fashion) in order to ensure the merge cartesian join will not occur anymore.</p>
  
  <pre><code>SELECT COL1 
  FROM SCHEMA.VIEW_NAME1 
  WHERE DATE_VAL &gt; (SELECT DATE_VAL FROM SCHEMA.VIEW_NAME2)
  </code></pre>
  
  <p>After reviewing a similar question "<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>", the problem seems to be "<em>Oracle doesn't know that (SELECT DATE_VAL FROM SCHEMA.VIEW_NAME2) returns one single result. So it's assuming that it will generate lots of rows.</em>"</p>
  
  <p>Is there some way to tell the Oracle optimizer that the sub-select will only return one row?</p>
  
  <p><strong>Would using a function that returns a datetime value in place of the sub-select help, assuming that the optimizer would then know that the function can only return one value?</strong></p>
  
  <pre><code>SELECT COL1 
  FROM SCHEMA.VIEW_NAME1
  WHERE DATE_VAL &gt; SCHEMA.FN_GET_DATE_VAL()
  </code></pre>
  
  <p>The Oracle DBA recommended using a WITH statement, which seems like it will work, but we were wondering if there were any shorter options.</p>
  
  <pre><code>with mx_dt as (SELECT DATE_VAL FROM SCHEMA.VIEW_NAME2)
  SELECT COL1
  FROM SCHEMA.VIEW_NAME1, mx_dt a
  WHERE DATE_VAL &gt; a.DATE_VAL
  </code></pre>