10204升级到10205后同一sql报告ora-01719

admin管理员组

文章数量:1596403

OS： HP
ORACLE： 10205
上周将该数据库从10204升级到10205，之后就有一条sql一直报告ora-01719
该sql结构如下
select a.xx
...
b.xx
from a, b
where a.class ='D'
and a.cd in ('A','E')
and ((a.cust_id = b.cust_id(+)) and 'D'='F') or
('D'='D') and (a.cust_id = b.cust_id(+)).

没有升级前，该sql一直相安无事，升级后就一直运行不成功
登陆数据库查看，allow (+) in OR clause 明明已经存在了

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

OPTIMIZER_FEATURE_ENABLE

-------------------------

DESCRIPTION

----------------------------------------------------------------

10.2.0.5

allow (+) in OR clause

且_eliminate_common_subexpr一直为true，从未改动过；

何以升级了反而出错哪

我尝试在会话级别将optimizer_feature_enable设置为10.2.0.4，然后运行sql，依旧报错

表b的cust_id为not null

发了SR给ORACLE，收到回复如下

In Oracle 10.2.0.4 and before, there is a bug in "common subexpression elimination" function. The bug number is 5346187. It can lead wrong transformation.
It was fixed on 10.2.0.5.

If setting the hidden parameter "_eliminate_common_subexpr" to false to disable the "common subexpression elimination", the ORA-1719 error will be report.
Please refer Bug 5346187, Note 5346187.8 and the last test case.


We recommend you to contact your developer to modify the SQL statement.
For example,

Using "unoin all" instead of "OR" operator.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production



SQL> alter session set "_eliminate_common_subexpr"=false;

Session altered.

SQL>
SQL> select d.deptno,e.empno,e.deptno from
dept d, emp e
where d.deptno in(10,20)
and d.deptno = e.deptno(+) and 'D'='D'
or (d.deptno = e.deptno(+) and 'D' = 'F' and d.deptno in(40)); 2 3 4 5
or (d.deptno = e.deptno(+) and 'D' = 'F' and d.deptno in(40))
*
ERROR at line 5:
ORA-01719: outer join operator (+) not allowed in operand of OR or IN


SQL> alter session set "_eliminate_common_subexpr"=true;

Session altered.

SQL> select d.deptno,e.empno,e.deptno from
dept d, emp e
where d.deptno in(10,20)
and d.deptno = e.deptno(+) and 'D'='D'
or (d.deptno = e.deptno(+) and 'D' = 'F' and d.deptno in(40)); 2 3 4 5

DEPTNO EMPNO DEPTNO
---------- ---------- ----------
20 7369 20
20 7566 20
10 7782 10
20 7788 20
10 7839 10
20 7876 20
20 7902 20
10 7934 10

8 rows selected.

也就是说这种sql语法本身就是一个bug才会让其编译通过的，10205修复了这个bug；

报告ora-1719才是正常行为，且一旦该bug修复，无法通过_fix_control='7148689:off'
修复，唯一的方法是修改sql

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub/15480802/viewspace-714744/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub/15480802/viewspace-714744/

本文标签： 升级到报告ORAsql