Oracle Error – Resolving ORA-02070: Database Does Not Support Antijoin

oracleoracle-11g-r2

I don't think this is even a question, but in case someone has seen something like this before or has any thoughts besides 'file a bug with Oracle' (which I'm doing), I'd love to hear it.

create table pat (patientid VARCHAR2(32), pcpid VARCHAR2(32), bval1 NUMBER(1), bval2 NUMBER(1));
create table tab (setmemberid VARCHAR2(32), uuid VARCHAR2(32));


SELECT COUNT(patientId) n FROM (
SELECT DISTINCT pat.patientId, pat.bval1, pat.bval2
FROM pat
 LEFT OUTER JOIN 
 (SELECT /*+ CARDINALITY(tab 10000) */ setmemberid FROM tab WHERE uuid = '7C06216F-EFC8-418A-A6D9-77C7F6FE7AB1') fsq1 
 ON pat.pcpid = fsq1.setmemberid 
WHERE fsq1.setmemberid IS NULL
) pat
GROUP BY GROUPING SETS ((pat.bval1), (pat.bval2), ())
;

If I remove the CARDINALITY hint query works fine.

Best Answer

Got a workaround from Oracle:

/*+ opt_param('_simple_view_merging', 'false') */

or

alter session set "_simple_view_merging"=false;

So the following query works:

SELECT COUNT(patientId) n FROM (
SELECT /*+ opt_param('_simple_view_merging', 'false') */ DISTINCT pat.patientId, pat.bval1, pat.bval2
FROM pat
 LEFT OUTER JOIN 
 (SELECT /*+ CARDINALITY(tab 10000) */ setmemberid FROM tab WHERE uuid = '7C06216F-EFC8-418A-A6D9-77C7F6FE7AB1') fsq1 
 ON pat.pcpid = fsq1.setmemberid 
WHERE fsq1.setmemberid IS NULL
) pat
GROUP BY GROUPING SETS ((pat.bval1), (pat.bval2), ())
;
Related Question