今天王工处理了一个关于nvl的Bug。
这个问题十分诡异，发生在Oracle的特定版本，特定用法。
王工不仅明确了Bug发生的条件，还推断了大致的原因。
王工的技术总能让我联想到海...

版本:            Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

触发条件:     1.NVL函数第一个参数的字段为NOT NULL
                  2.使用FULL JOIN
                  3.NVL函数第一个参数的字段在FULL JOIN 中被用在子查询中

大致原因: 上述触发条件同时成立，Oracle认为非空的字段可以忽略NVL函数，但是FULL JOIN会添加NULL值的情况，它没有考虑到。
                  不过仅仅限于FULL JOIN，LEFT JOIN的结果是正确的。

--创建实验表

create table t1 as

select rownum id from dual connect by level<5;

create table t2 as

select rownum id from dual connect by level<10;

--整理数据

delete from t2 where id <3;

commit;

--添加主键

alter table t1 add constraint pk_t1 primary key (id);

alter table t2 add constraint pk_t2 primary key (id);

--测试语句

select * from t1 full outer join

(

    select * from t2

) t2

on t1.id=t2.id

where nvl(t2.id,t1.id)=1;

查看执行计划，发现没有应用NVL过滤

而同样的语句，没有同时达到触发条件，或者使用NVL2等，则没有这个问题。
例如没有子查询的这个条件，则结果正常。

什么时候才能像王工一样厉害呢

ITPub博客

Oracle关于nvl的一个Bug

最新文章