Why would this query cause a Merge Cartesian Join in Oracle
- by decompiled
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.
