Why would this query cause a Merge Cartesian Join in Oracle

Posted by decompiled on Stack Overflow See other posts from Stack Overflow or by decompiled
Published on 2010-06-18T13:44:49Z Indexed on 2010/06/18 14:43 UTC
Read the original article Hit count: 157

Filed under:

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.

© Stack Overflow or respective owner

Related posts about Oracle