oracle sql优化之 not in子句包含null返回结果为空的研究

开发人员说一条 sql 说没有返回值，但是子查询和外部单独执行，能看到有结果集，我在解决这个问题的时候发现了一个关于 null 的很有意思的问题。现在还原整个过程。

 

简化后的 sql 本质实际上是很简单的一条，这里就不贴了，实验环境直接还原过程：

 

创建测试表：

create table t_dept as select * from   scott.dept; create table t_emp as select * from   scott.emp; insert into t_emp(deptno,ename)  values(null,'MINGSHUO');   -- 在 emp 表中插入一条数据， deptno 列为 null commit;

 

 

数据结构如下：

SQL> select distinct deptno from   t_emp;         DEPTNO ----------           30             20           10 SQL> select distinct deptno from   t_dept;         DEPTNO ----------           30           20           40           10

 

此时发起一条查询，查询不在 emp 中但是在 dept 表中部门信息：

SQL> select * from t_dept where deptno   not in (select deptno from t_emp where deptno is not null);         DEPTNO DNAME          LOC ---------- -------------- -------------           40 OPERATIONS     BOSTON

此时是有结果返回的。

 

然后把子查询中的 where dept is not null 去掉，再次运行查询：

SQL> select * from t_dept where deptno   not in (select deptno from t_emp);   no rows selected

此时返回结果为空。

 

这里很多人存在疑惑，为什么子查询结果集包括 null 就会出问题，比如 t_dept.deptno 为 40 的时候， 40 not in （ 10,20,30,null ）也成立啊。毕竟 oracle 查询优化器不如人脑智能懂得变通，查看执行计划就比较容易明白了。

Execution Plan ---------------------------------------------------------- Plan hash value: 2864198334   ----------------------------------------------------------------------------- | Id    | Operation          | Name   | Rows    | Bytes | Cost (%CPU)| Time       | ----------------------------------------------------------------------------- |     0 | SELECT STATEMENT   |        |       4 |   172 |     5    (20)| 00:00:01 | |*    1 |  HASH   JOIN ANTI NA |        |     4 |     172 |     5  (20)| 00:00:01 | |     2 |   TABLE ACCESS FULL| T_DEPT   |     4 |   120 |       2   (0)| 00:00:01 | |     3 |   TABLE ACCESS FULL|   T_EMP  |    15 |     195 |     2   (0)| 00:00:01 | -----------------------------------------------------------------------------   Predicate Information (identified by   operation id): ---------------------------------------------------        1 - access("DEPTNO"="DEPTNO")   Note -----      - dynamic sampling used for this statement (level=2)

注意到这里 id 1 是 HASH JOIN ANTI NA 。这时候就想起来了， not in 是对 null 值敏感的。所以普通反连接是不能处理 null 的，因此 oracle 推出了改良版的能处理 null 的反连接方法，这种方法被称为 "Null-Aware Anti Join" 。 operation 中的关键字 NA 就是这么来的了。

 

在 Oracle 11gR2 中， Oracl 通过受隐含参数 _OPTIMIZER_NULL_AWARE_ANTIJOIN 控制 NA ，其默认值为 TRUE ，表示启用 Null-Aware Anti Join 。

 

下面禁用掉，然后再观察：

alter session set   "_optimizer_null_aware_antijoin" = false;  再次执行： select * from t_dept where deptno   not in (select deptno from t_emp);

 

执行计划如下：

Execution Plan ---------------------------------------------------------- Plan hash value: 393913035   ----------------------------------------------------------------------------- | Id    | Operation          | Name   | Rows    | Bytes | Cost (%CPU)| Time       | ----------------------------------------------------------------------------- |     0 | SELECT STATEMENT   |        |       1 |    30 |     4     (0)| 00:00:01 | |*    1 |  FILTER            |        |       |         |            |          | |     2 |   TABLE ACCESS FULL| T_DEPT   |     4 |   120 |       2   (0)| 00:00:01 | |*    3 |   TABLE ACCESS FULL|   T_EMP  |    14 |     182 |     2   (0)| 00:00:01 | -----------------------------------------------------------------------------   Predicate Information (identified by   operation id): ---------------------------------------------------        1 - filter( NOT EXISTS (SELECT 0 FROM "T_EMP"   "T_EMP" WHERE                 LNNVL("DEPTNO"<>:B1)))      3 - filter(LNNVL("DEPTNO"<>:B1))   Note -----      - dynamic sampling used for this statement (level=2)

 

 

lnnvl 用于某个语句的 where 子句中的条件，如果条件为 true 就返回 false ；如果条件为 UNKNOWN 或者 false 就返回 true 。该函数不能用于复合条件如 AND, OR, or BETWEEN 中。

此时比如 t_dept.deptno 为 40 的时候，（ 40 not in 10 ） and （ 40 not in 20 ） and （ 40 not in 30 ） and （ 40 not in null ），注意这里是 and “并且”，条件都需要满足。

结果是 true and true and true and false 或者 unknow 。经过 lvnnvl 函数后：

false and false and false and true, 结果还是 false 。所以自然就不会有结果了。

 

 

如果还不明白的话换个比较直观的写法：

SQL> select * from t_dept where deptno   not in (10,20,null);   no rows selected     Execution Plan ---------------------------------------------------------- Plan hash value: 719542577   ---------------------------------------------------------------------------- | Id    | Operation         | Name   | Rows    | Bytes | Cost (%CPU)| Time       | ---------------------------------------------------------------------------- |     0 | SELECT STATEMENT  |        |       1 |    30 |     2     (0)| 00:00:01 | |*    1 |  TABLE ACCESS FULL| T_DEPT   |     1 |    30 |       2   (0)| 00:00:01 | ----------------------------------------------------------------------------   Predicate Information (identified by   operation id): ---------------------------------------------------        1 - filter("DEPTNO"<>10 AND   "DEPTNO"<>20 AND                 "DEPTNO"<>TO_NUMBER(NULL))   Note -----      - dynamic sampling used for this statement (level=2)

过滤条件 "DEPTNO"<>10 AND "DEPTNO"<>20 AND "DEPTNO"<>TO_NUMBER(NULL) 因为最后一个 and 条件，整个条件恒为 flase 或者 unkonw 。

所以 not in 的子查询中出现 null 值，无返回结果。

 

 

这种时候其实可以用 not exists 写法和外连接代替：

not exists 写法：

其实这种写法前面已经出现过了。就在禁用掉反连接之后，出现在 fileter 中的， oracle 在内部改写 sql 时可能就采用了这种写法：

select *     from t_dept d  where not exists (select 1 from t_emp e   where d.deptno = e.deptno);

 

 

外连接的写法：

select d.* from t_dept d, t_emp e where   d.deptno=e.deptno(+) and e.deptno is null;

 

 

同事还给我展示了丁俊的实验，里面有复合列的讨论，结论简单明了，这里我就直接搬过来吧，如下：

/** 根据 NULL 的比较和逻辑运算规则， OR 条件有一个为 TRUE 则返回 TRUE ，全为 FALSE 则结果为 FALSE ，其他为 UNKNOWN ，比如 (1,2) not in (null,2) 则相当于 1 <> null or 2 <> 2, 那么明显返回的结果是 UNKNOWN ，所以不可能为真，不返回结果，但是 (1,2) not in (null,3) 相当于 1 <> null or 2 <> 3, 因为 2<>3 的已经是 TRUE, 所以条件为 TRUE ，返回结果，也就说明了为什么 Q2 中的 测试是那样的结果 **/   看个简单的结果： SQL> SELECT * FROM DUAL WHERE (1,2)   not in ( (null,2) );   DUMMY ----- SQL> SELECT * FROM DUAL WHERE (1,2)   not in ( (null,3) );   DUMMY ----- X

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31480688/viewspace-2168489/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31480688/viewspace-2168489/