Oracle 12c R1目前使用的也是越来越多了，但是在实际的使用中还是有不少的问题。由于所处的行业对准确性比较敏感，而12cR1中的优化器确实存在着挺多的坑会导致结果不准确。其中之一就是使用外连接可能会导致结果不正确。

场景还原

创建一张测试表：

create table BUG.T_BUGTEST_01(ID NUMBER,NAME VARCHAR2(30),ADDR VARCHAR2(100),PHONE VARCHAR2(30),MAIL VARCHAR2(100),AGE NUMBER);

往测试表中插入两条数据：

insert into BUG.T_BUGTEST_01 values (1,'BUG1','LBS1111111','1234567890','bug1@test.com',4);
insert into BUG.T_BUGTEST_01 values (2,'BUG2','LBS2222222','1234567890','bug2@test.com',4);
commit;

环境已经准备就绪了，很简单的场景。然后我们来看看执行下面的这条SQL会发生什么？

select t1.*, p.maddr from bug.t_bugtest_01 t1,
(select 'notused' maddr from dual where 1 = 0) p
where t1.addr = p.maddr(+) and t1.id = 1
and t1.addr in ('LBS1111111','LBS2222222')
order by t1.addr;

这是一条很简单的SQL，即时不用执行，我们也能很容易得出SQL的结果是什么。但是实际的执行结果呢？

SQL> select t1.*, p.maddr from bug.t_bugtest_01 t1,
2 (select 'notused' maddr from dual where 1 = 0) p
3 where t1.addr = p.maddr(+) and t1.id = 1
4 and t1.addr in ('LBS1111111','LBS2222222')
5 order by t1.addr;
no rows selected

no rows！！！真的是no rows，不管执行多少次都是no rows。但是如果我们把优化器的特性进行降级：

SQL> alter session set optimizer_features_enable = '11.2.0.4';
Session altered.
SQL> select t1.*, p.maddr from bug.t_bugtest_01 t1,
2 (select 'notused' maddr from dual where 1 = 0) p
3 where t1.addr = p.maddr(+) and t1.id = 1
4 and t1.addr in ('LBS1111111','LBS2222222')
5 order by t1.addr;
ID NAME ADDR PHONE MAIL AGE MADDR
---------- ------------------------------ ---------------------------------------------------------------------------------------------------- ------------------------------ ---------------------------------------------------------------------------------------------------- ---------- -------
1 BUG1 LBS1111111 1234567890 bug1@test.com

这个结果才是正确的。

我们简单梳理下上面这个SQL的几个特征：
1、参与外连接的对象中有视图（p）
2、视图（p）的结果为空集
3、select选择中有引用p的列
4、p的列参与外连接(p.maddr)
5、表t1用来和p的列进行连接的列还有额外的选择条件（t1.addr in）

上述条件刚好触发了12cR1的BUG 21459392。

解决方法
1、将优化器降低： alter session set Optimizer_features_enable = '11.2.0.4';

2、修改隐含参数： alter system set "_optimizer_ansi_join_lateral_enhance"=false;
3、安装补丁： Patch 21459392

ITPub博客

Oracle Database 12c查询优化器的缺陷-外连接导致结果不准确

最新文章