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.
SELECT COL1
FROM SCHEMA.VIEW_NAME1
WHERE DATE_VAL > (SELECT DATE_VAL FROM SCHEMA.VIEW_NAME2)
After reviewing a similar question "Why would this query cause a Merge Cartesian Join in Oracle", the problem seems to be "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."
Is there some way to tell the Oracle optimizer that the sub-select will only return one row?
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?
SELECT COL1
FROM SCHEMA.VIEW_NAME1
WHERE DATE_VAL > SCHEMA.FN_GET_DATE_VAL()
The Oracle DBA recommended using a WITH statement, which seems like it will work, but we were wondering if there were any shorter options.
with mx_dt as (SELECT DATE_VAL FROM SCHEMA.VIEW_NAME2)
SELECT COL1
FROM SCHEMA.VIEW_NAME1, mx_dt a
WHERE DATE_VAL > a.DATE_VAL
I wouldn't worry about the Cartesian join, because the subquery is only returning one row (at most). Otherwise, you would get a "subquery returns too many rows" error.
It is possible that Oracle runs that the subquery once for each comparison -- possible, but the Oracle optimizer is smart and I doubt that would happen. However, it is easy enough to phrase this as a JOIN:
SELECT n1.COL1
FROM SCHEMA.VIEW_NAME1 n1 JOIN
SCHEMA.VIEW_NAME2 n2
ON n1.DATE_VAL > n2.DATE_VAL;
However, it is possible that this execution plan is even worse, because you have not specified that n2 is only supposed to return (at most) one value.