小心not in子查询的null值！

sql语句如下：
A语句：
SELECT *
  FROM t1
 WHERE t1.bm = 'T_SFZJ_GRJBXX'
   AND NOT EXISTS (SELECT 1
                     FROM t3
                    WHERE t3.grbh_ds = t1.grbh_ds);
执行计划：
Plan
SELECT STATEMENT  CHOOSECost: 573  Bytes: 2,111  Cardinality: 1          
    3 NESTED LOOPS ANTI  Cost: 573  Bytes: 2,111  Cardinality: 1      
        1 TABLE ACCESS FULL T1 Cost: 62  Bytes: 1,073,100  Cardinality: 511  
        2 INDEX UNIQUE SCAN UNIQUE IDX_T3_GRBH_DS Cost: 1  Bytes: 12,845,041  Cardinality: 1,167,731  

B语句：
SELECT *
  FROM t1
 WHERE t1.bm = 'T_SFZJ_GRJBXX'
   AND grbh_ds NOT IN (SELECT t3.grbh_ds
                         FROM t3);
执行计划：
Plan
SELECT STATEMENT  CHOOSECost: 116,386  Bytes: 54,600  Cardinality: 26          
    3 FILTER      
        1 TABLE ACCESS FULL T1 Cost: 62  Bytes: 54,600  Cardinality: 26  
        2 TABLE ACCESS FULL T3 Cost: 4,474  Bytes: 642,389  Cardinality: 58,399  


C语句：
SELECT *
  FROM t1
 WHERE t1.bm = 'T_SFZJ_GRJBXX'
   AND grbh_ds NOT IN (SELECT t3.grbh_ds
                         FROM t3
                        WHERE grbh_ds IS NOT NULL);
执行计划：
Plan
SELECT STATEMENT  CHOOSECost: 86,174  Bytes: 54,600  Cardinality: 26          
    3 FILTER      
        1 TABLE ACCESS FULL T1 Cost: 62  Bytes: 54,600  Cardinality: 26  
        2 INDEX FULL SCAN UNIQUE IDX_T3_GRBH_DS Cost: 3,312  Bytes: 642,257  Cardinality: 58,387  

由于
A.t3.grbh_ds没有not null限制
B.null值在索引不保存，
所以类似grbh_ds NOT IN (SELECT t3.grbh_ds FROM t3)的条件，cbo使用全表扫描而不会扫描索引IDX_T3_GRBH_DS，性能很低，而且假如SELECT t3.grbh_ds FROM t3有null值，则不会B语句不会删除一条记录，因为NOT IN 语句实质上等同于使用=比较每一值，假如测试为FALSE或者NULL，结果为比较失败。
见如下测试：

drop table t1;
create table t1(c1 char(10));
create index idx_c1 on t1(c1);

insert into t1 values('1');
insert into t1 values('3');
insert into t1 values('5');
insert into t1 values(null);
commit;

drop table t2;
create table t2(c2 char(10));
create index idx_c2 on t2(c2);

insert into t2 values('1');
insert into t2 values('2');
insert into t2 values('4');
insert into t2 values(null);

commit;

select * from t1 where c1 in (select c2 from t2);
select * from t1 where exists (select 1 from t2 where c1=c2);

select * from t1 where c1 not in (select c2 from t2);
select * from t1 where c1 not in (select nvl(c2,'X') from t2);
select * from t1 where not exists (select 1 from t2 where c1=c2);


TEST@TEST>
TEST@TEST>drop table t1;

表已删除。

已用时间:  00: 00: 00.09
TEST@TEST>create table t1(c1 char(10));

表已创建。

已用时间:  00: 00: 00.01
TEST@TEST>create index idx_c1 on t1(c1);

索引已创建。

已用时间:  00: 00: 00.01
TEST@TEST>
TEST@TEST>insert into t1 values('1');

已创建 1 行。

已用时间:  00: 00: 00.00
TEST@TEST>insert into t1 values('3');

已创建 1 行。

已用时间:  00: 00: 00.00
TEST@TEST>insert into t1 values('5');

已创建 1 行。

已用时间:  00: 00: 00.00
TEST@TEST>insert into t1 values(null);

已创建 1 行。

已用时间:  00: 00: 00.00
TEST@TEST>commit;

提交完成。

已用时间:  00: 00: 00.03
TEST@TEST>
TEST@TEST>drop table t2;

表已删除。

已用时间:  00: 00: 00.09
TEST@TEST>create table t2(c2 char(10));

表已创建。

已用时间:  00: 00: 00.01
TEST@TEST>create index idx_c2 on t2(c2);

索引已创建。

已用时间:  00: 00: 00.03
TEST@TEST>
TEST@TEST>insert into t2 values('1');

已创建 1 行。

已用时间:  00: 00: 00.03
TEST@TEST>insert into t2 values('2');

已创建 1 行。

已用时间:  00: 00: 00.01
TEST@TEST>insert into t2 values('4');

已创建 1 行。

已用时间:  00: 00: 00.01
TEST@TEST>insert into t2 values(null);

已创建 1 行。

已用时间:  00: 00: 00.01
TEST@TEST>
TEST@TEST>commit;

提交完成。

已用时间:  00: 00: 00.03
TEST@TEST>
TEST@TEST>select * from t1 where c1 in (select c2 from t2);

C1                                                                              
----------                                                                      
1                                                                               

已用时间:  00: 00: 00.04
TEST@TEST>select * from t1 where exists (select 1 from t2 where c1=c2);

C1                                                                              
----------                                                                      
1                                                                               

已用时间:  00: 00: 00.03
TEST@TEST>
TEST@TEST>select * from t1 where c1 not in (select c2 from t2);

未选定行

已用时间:  00: 00: 00.01
TEST@TEST>select * from t1 where c1 not in (select nvl(c2,'X') from t2);

C1                                                                              
----------                                                                      
3                                                                               
5                                                                               

已用时间:  00: 00: 00.04
TEST@TEST>select * from t1 where not exists (select 1 from t2 where c1=c2);

C1                                                                              
----------                                                                      
3                                                                               
5                                                                               
                                                                                

已用时间:  00: 00: 00.01