今天有兄弟反饋,有一條SQL在12C上沒有問題,在11g上就報錯,報錯內容是ORA-01417: 表可以外部連接到至多一個其他的表。分析了下SQL,抽象為下列的例子。
在12c下沒有問題:
SQL> select * from v$version; BANNER CON_ID -------------------------------------------------------------------------------- ---------- Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0 PL/SQL Release 12.1.0.1.0 - Production 0 CORE 12.1.0.1.0 Production 0 TNS for Linux: Version 12.1.0.1.0 - Production 0 NLSRTL Version 12.1.0.1.0 - Production 0 drop table test1 purge; drop table test2 purge; drop table test3 purge; create table test1 (id number primary key); create table test2 (id number); create table test3 (dept_id number,measure_id number); insert into test1 values(1); insert into test1 values(2); insert into test1 values(3); insert into test2 values(10); insert into test2 values(20); insert into test3 values(1,10); commit; SQL> select * from test1, test2, test3 where test1.id = test3.dept_id(+) and test2.id = test3.measure_id(+); ID ID DEPT_ID MEASURE_ID ---------- ---------- ---------- ---------- 1 10 1 10 2 10 2 20 3 20 1 20 3 10已選擇6行。
在11g下有問題:
SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production PL/SQL Release 11.2.0.1.0 - Production CORE 11.2.0.1.0 Production TNS for Linux: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production SQL> select * from test1, test2, test3 where test1.id = test3.dept_id(+) and test2.id = test3.measure_id(+); where test1.id = test3.dept_id(+) *
--把test1和test2聯合起來,再與test3關聯,問題解決 SQL> select * from (select test1.id dept_id, test2.id measure_id from test1, test2) a, test3 where a.dept_id = test3.dept_id(+) and a.measure_id = test3.measure_id(+);