Why would this query cause a Merge Cartesian Join in Oracle

Question

I have a query that was recently required to be modified.

Here's the original

SELECT RTRIM (position) AS "POSITION",
   .  // Other fields
   .
   .
   FROM schema.table x WHERE hours > 0 
    AND pay = 'RGW'
    AND NOT EXISTS( SELECT position FROM schema.table2 y where  y.position = x.position )

Here's the new version

SELECT RTRIM (position) AS "POSITION",
   .  // Other fields
   .
   .
   FROM schema.table x WHERE hours > 0 
    AND pay = 'RGW'
    AND NOT EXISTS( SELECT position FROM  schema.table2 y where y.date = get_fiscal_year_start_date (SYSDATE) AND y.position = x.position )

The UDF get_fiscal_year_start_date() returns the fiscal year start date of the date parameter. The first query runs fine, but the second creates a merge Cartesian join. I looked at the indexes on the tables and found that position and date were both indexed. My question for you stackoverflow is why would the addition of y.date = get_fiscal_year_start_date (SYSDATE) cause a merge cartesian join in Oracle 10g.

Answer 1

The problem is, Oracle doesn't know that get_fiscal_year_start_date (SYSDATE) returns one single result. So it's assuming that it will generate lots of rows.

Obviously I don't have a test harness to hand, but this version of your query ought to banish the merge cartesian join.

SELECT RTRIM (position) AS "POSITION", 
.  // Other fields 
. 
. 
FROM schema.table x 
     , ( select get_fiscal_year_start_date (SYSDATE) as fiscal_year 
         from dual ) fy
WHERE hours > 0  
AND pay = 'RGW' 
AND NOT EXISTS( SELECT position 
                FROM  schema.table2 y 
                where y.date = fy.fiscal_year
                AND y.position = x.position ) 

Oracle knows that DUAL has a single row, and hence that the sub-query will return one value.