本文简要介绍了关联子查询、非关联子查询、IN & EXISTS 、 NOT IN & NOT EXISTS之间的区别；同时对不同数据库版本下CBO对IN & EXISTS & NOT IN & NOT EXISTS的处理做了一定的阐述。

       本文可以任意转载，转载时请务必以超链接形式标明文章原始出处和作者信息及本声明

        http://blog.itpub.net/post/11/5353

1.      1.  简介

本文简要介绍了关联子查询、非关联子查询、IN & EXISTS 、 NOT IN & NOT EXISTS之间的区别；同时对不同数据库版本下CBO对IN & EXISTS & NOT IN & NOT EXISTS的处理做了一定的阐述。

       2.         os、数据库版本以及测试数据

os：windows 2000 server sp4

db：oracle 10.1.0.2

set time on

set timing on

drop table outer_large_t

/

create table outer_large_t

(id number,

c1 varchar2(100),

c2 varchar2(100)

)

/

create index idx_outer_large_t on outer_large_t(id)

/

drop table outer_small_t

/

create table outer_small_t

as select *from outer_large_t

where 1=2

/

create index idx_outer_small_t_id on outer_small_t(id)

/

drop table inner_large_t

/

create table inner_large_t

(id number,

c3 varchar2(100),

c4 varchar2(100)

)

/

create index idx_inner_large_t_1 on inner_large_t(id,c3)

/

drop table inner_small_t

/

create table inner_small_t

(id number,

c3 varchar2(100),

c4 varchar2(100)

)

/

create index idx_inner_small_t on inner_small_t(id,c3)

/

3.      2.关联子查询和非关联子查询

测试数据:

truncate table outer_large_t

/

truncate table inner_large_t

/

declare

begin

  for i in 1..50000 loop

    insert into outer_large_t values (i,'test','test');   

  end loop;

  for i in 30000..100000 loop

    insert into inner_large_t values (i,'test','test');   

  end loop;

  commit;

end;

/

analyze table outer_large_t compute statistics for table for all indexes

/

analyze table inner_large_t compute statistics for table for all indexes

/

非关联子查询形如：

select count(*) from outer_large_t

where id not in

(select id from inner_large_t)

/

子查询与父查询没有关联。

关联子查询形如：

select count(*) from outer_large_t outer_t

where not exists

(select id from inner_large_t where id = outer_t.id)

/

子查询与父查询存在关联id = outer_t.id。

非关联子查询对于exists和not exists是没有意义的。

看如下实验：

11:17:00 test@GZSERVER> select count(*) from outer_large_t

11:17:02   2  where id not in

11:17:02   3  (select id from inner_large_t)

11:17:02   4  /

  COUNT(*)

----------

     29999

已用时间:  00: 00: 00.04

11:17:02 test@GZSERVER> select count(*) from outer_large_t

11:17:02   2  where id in

11:17:02   3  (select id from inner_large_t)

11:17:02   4  /

  COUNT(*)

----------

     20001

已用时间:  00: 00: 00.01

11:17:02 test@GZSERVER> select count(*) from outer_large_t outer_t

11:17:02   2  where not exists

11:17:02   3  (select id from inner_large_t)

11:17:02   4  /

  COUNT(*)

----------

         0

已用时间:  00: 00: 00.00

11:17:02 test@GZSERVER> select count(*) from outer_large_t outer_t

11:17:02   2  where exists

11:17:02   3  (select id from inner_large_t)

11:17:02   4  /

  COUNT(*)

----------

     50000

已用时间:  00: 00: 00.00

11:17:03 test@GZSERVER>

非关联子查询使用not exists的话父查询总是返回0，使用exists总是返回父查询的查询结果集。