Oracle how to avoid merge cartesian join in execution plan?

Question

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

Use a group function in the subselect like `SELECT MIN(DATE_VAL) FROM VIEW_NAME2` — Glenn, Dec 12 '17 at 19:36

score 1 · Answer 1 · answered Dec 13 '17 at 05:34

Try adding WHERE ROWNUM >= 1:

SELECT COL1 
FROM SCHEMA.VIEW_NAME1 
WHERE DATE_VAL > (SELECT DATE_VAL FROM SCHEMA.VIEW_NAME2 WHERE ROWNUM >= 1)

That predicate looks totally extraneous, or the kind of thing Oracle would just ignore, but the ROWNUM pseudo-function is special. When Oracle sees it, it thinks "these rows must be returned in order, I better not do any query transformations". Which means that it won't try to push predicates, merge views, etc. Which means VIEW_NAME1 will be run separately from VIEW_NAME2 and they now both run just as fast as before.

You'll probably still see a Cartesian product in the explain plan but hopefully only near the top, between the two view result sets. If there really is only one row returned then a Cartesian product is probably the right operation.

score 0 · Accepted Answer · answered Dec 12 '17 at 19:35

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.

Hi Gordon, I forgot to mention, the Cartesian join caused the TEMP db to fill up (over 100 gigs) even though the tables underneath the views are very small (1000s of records). — Matthew Walk, Dec 12 '17 at 21:37

score 0 · Answer 3 · answered Dec 12 '17 at 19:39

An aggregate function in the sub-select ensures a single row is returned. Probably would be a good hint to the optimizer and if there is only 1 row in VIEW_NAME2 then the result of the sub-select is the same.

SELECT COL1 
  FROM SCHEMA.VIEW_NAME1 
  WHERE DATE_VAL > (SELECT MIN(DATE_VAL) FROM SCHEMA.VIEW_NAME2)

Oracle how to avoid merge cartesian join in execution plan?

3 Answers3