以下sql在9208中运行正常,升级到10.2.0.5后运行报错 ORA-01719 outer join operator (+) not allowed in operand of OR or IN
有通过db link连接到远端资料库。
SELECT BM.MOLD_CODE,P.PARTS_CODE,BD.USELESS_NUMBERS,BD.DEFECT_CODE
FROM B_C_HGMASTER@BOND BM,B_LINE@BOND BL,PARTS P,B_C_HGDETAIL@BOND BD
WHERE BM.FACTORY_CODE='AAA'
AND BM.JY_DATE>='2013/09/16'
AND BM.JY_DATE<='2013/09/21'
AND BM.MOLD_CODE='BBB-CCC'
AND BM.SECTION_CODE in('H3001','H3002','3001','3002')
AND BM.DATA_NAME=BD.DATA_NAME
AND BM.SECTION_CODE=BL.LINE_CODE(+)
AND BM.PARTS_CODE=P.PARTS_CODE(+)
---------------------------------------------------------------------
查找网络得到以下信息。
这是一个oracle bug修复造成的问题
以下是oracle support的回复
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了,把远端的3个表(这段代码当初就不合理,既然4个表中有3个是远端的,就应该先在远端处理完),先在远端作关联建立视图,然后再同本地关联。
create or replace view v_B_C_HGMASTER as
SELECT bm.factory_code,bm.section_code,BM.JY_DATE,BM.MOLD_CODE,BM.PARTS_CODE,BD.USELESS_NUMBERS,BD.DEFECT_CODE
FROM B_C_HGMASTER BM,B_LINE BL,B_C_HGDETAIL BD
WHERE BM.DATA_NAME=BD.DATA_NAME
AND BM.SECTION_CODE=BL.LINE_CODE(+)
---------------
SELECT BM.MOLD_CODE,P.PARTS_CODE,Bm.USELESS_NUMBERS,Bm.DEFECT_CODE FROM v_B_C_HGMASTER@bond bm,PARTS P
WHERE bm.PARTS_CODE=P.PARTS_CODE(+)
and BM.FACTORY_CODE='AAA
AND BM.JY_DATE>='2013/09/16'
AND BM.JY_DATE<='2013/09/21'
AND BM.MOLD_CODE='BBB-CCC'
AND BM.SECTION_CODE in('H3001','H3002','3001','3002')
-------同样ERROR--------
select e.employee_id, e.last_name, d.department_name
from employees e, departments dwhere e.department_id = d.department_id(+)
OR e.job_id = 'MGR';