今天在oracle9206中使用full outer join遇到bug，执行没有响应，例子如下：

[@more@]SQL> select * from p1;

MPN CNT
----------- ----------
1 5
2 6

SQL> select * from p2;

MPN CNT
----------- ----------
2 8
3 9

简单点的没有问题：

SQL> select a.mpn,a.cnt,b.mpn,b.cnt
2 from (select mpn,sum(cnt) cnt from p1 group by mpn) a
3 full outer join
4 (select mpn,sum(cnt) cnt from p2 group by mpn) b
5 on a.mpn=b.mpn;

MPN CNT MPN CNT
----------- ---------- ----------- ----------
2 6 2 8
1 5
3 9

如果出现统计多字段查询就无相应了：

SQL> select *
2 from (select mpn,sum(cnt) cnt,count(cnt) from p1 group by mpn) a
3 full outer join
4 (select mpn,sum(cnt) cnt,count(cnt) from p2 group by mpn) b
5 on a.mpn=b.mpn;

10g版本1中反映稍有不同，报错：ORA-03113:通信通道的文件结束

ITPub博客

oracle9i(10g)中使用full outer join遇到bug

最新文章