ORA-01719 is partially relaxed

You most likely have seen this error before:

ORA-01719: outer join operator (+) not allowed in operand of OR or IN
Cause: An outer join appears in an or clause.
Action: If A and B are predicates, to get the effect of (A(+) or B), try (select where (A(+) and not B)) union all (select where (B)).

In 11gR2 it is no longer raised for predicates like column(+) IN (constant1, constant2, …).

drop table t2 cascade constraints purge;
drop table t1 cascade constraints purge;

create table t1 as 
select rownum id1, rownum id2, mod(rownum, 10) x from dual connect by level <= 10;

create table t2 as 
select t11.id1 id, trunc(dbms_random.value(0, 10)) y
  from t1 t11, t1 t12
 where t11.x = 0;

exec dbms_stats.gather_table_stats(user, 't1')
exec dbms_stats.gather_table_stats(user, 't2')

explain plan for
select t1.id1, t1.x, t2.y
  from t1, t2
 where t1.id1 = t2.id(+)
   and t2.y(+) in (10, 11);

select * from table(dbms_xplan.display);

explain plan for
select t1.id1, t1.x, t2.y
  from t1, t2
 where t1.id1 = t2.id(+)
    or t1.x = t2.y;

select * from table(dbms_xplan.display);

explain plan for
select t1.id1, t1.x, t2.y
  from t1, t2
 where t2.id(+) in (t1.id1, t1.id2);

select * from table(dbms_xplan.display);

All queries fail in 10.2.0.4 with the same ORA-01719 error. But in 11gR2 the first one isn’t:

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    10 |   120 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |      |    10 |   120 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |    10 |    60 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T2   |     2 |    12 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."ID1"="T2"."ID"(+))
   3 - filter("T2"."Y"(+)=10 OR "T2"."Y"(+)=11)

This enhancement seems to be a result of a fix for performance related issue in ANSI-style join (which is quite surprising). See Bug 6610822 for more details.
The fix will be included in the upcoming 10.2.0.5:

SQL> select optimizer_feature_enable, description from v$session_fix_control where session_id = userenv('sid') and bugno = 6610822
  2  /

OPTIMIZER_FEATURE_ENABLE  DESCRIPTION
------------------------- ----------------------------------------------------------------
10.2.0.5                  allow (+) in OR clause

About these ads