ITPub博客

首页 > Linux操作系统 > Linux操作系统 > NOT IN & NOT EXISTS

NOT IN & NOT EXISTS

原创 Linux操作系统 作者:husthxd 时间:2009-03-27 14:40:46 0 删除 编辑
小心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
 

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

请登录后发表评论 登录
全部评论
ITPUB数据库版块资深版主,对Oracle、PostgreSQL有深入研究。

注册时间:2007-12-28

  • 博文量
    1398
  • 访问量
    3843237