Why this query will cause Cartesian Merge merge in Oracle

Question

I have a request that was recently required for a change.

Here is 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 is 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 )

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

Answer 1

, Oracle , get_fiscal_year_start_date (SYSDATE) . , .

, , .

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 , DUAL , , , .