ITPub博客

首页 > Linux操作系统 > Linux操作系统 > IN/EXISTS/NOT IN/NOT EXISTS(2-1)

IN/EXISTS/NOT IN/NOT EXISTS(2-1)

原创 Linux操作系统 作者:husthxd 时间:2004-11-19 00:00:00 0 删除 编辑

4.         in & exist     

          1)        in/exists等价的连接查询         

     2)        inexists性能比较


1)        in/exists等价的连接查询

select * from T1 where x in ( select y from T2 where z = Z) 等价于

select t1.*from t1 , (select *from t2 where z = Z) t2 where t1.x = t2.x而非

       select t1.* from t1,t2 where t1.x = t2.x and t2.z = Z

       select * from T1 where x exists ( select null from T2 where t1.x = t2.y and z = Z)等价于

       select distinct t1.* from T1 , T2 where t1.x = t2.y and t2.z = Z

(如果在t1xt2y上均有唯一主键,则distinct可省略)

       测试如下:

truncate table outer_large_t

/

truncate table inner_large_t

/

insert into outer_large_t values(1,'test1','test1')

/

insert into outer_large_t values(2,'test2','test3')

/

insert into outer_large_t values(3,'test3','test2')

/

insert into inner_large_t values(1,'test1','test')

/

insert into inner_large_t values(1,'test1','test')

/

insert into inner_large_t values(1,'test3','test')

/

commit

/

 

test@GZSERVER> select * from outer_large_t where id in (select id from inner_lar

ge_t where c3 = 'test1')

  2  /

 

          ID C1         C2

------------ ---------- ----------

           1 test1      test1

 

test@GZSERVER> select t1.* from outer_large_t t1,inner_large_t t2

  2  where t1.id = t2.id

  3  and t2.c3 = 'test1'

  4  /

 

          ID C1         C2

------------ ---------- ----------

           1 test1      test1

           1 test1      test1

 

test@GZSERVER> select t1.* from outer_large_t t1,(select distinct id from inner_

large_t t2 where c3 = 'test1') t2

  2  where t1.id = t2.id

  3  /

 

          ID C1         C2

------------ ---------- ----------

           1 test1      test1

 

test@GZSERVER> select * from outer_large_t t1

  2  where exists (select null from inner_large_t t2 where t1.id = t2.id and t2.

c3 = 'test1')

  3  /

 

          ID C1         C2

------------ ---------- ----------

           1 test1      test1

 

test@GZSERVER> select t1.* from outer_large_t t1,inner_large_t t2

  2  where t1.id = t2.id

  3  and t2.c3 = 'test1'

  4  /

 

          ID C1         C2

------------ ---------- ----------

           1 test1      test1

           1 test1      test1

 

test@GZSERVER> select distinct t1.* from outer_large_t t1,inner_large_t t2

  2  where t1.id = t2.id

  3  and t2.c3 = 'test1'

  4  /

 

          ID C1         C2

------------ ---------- ----------

           1 test1      test1

  2)      inexists性能比较

测试数据

truncate table outer_large_t

/

truncate table outer_small_t

/

truncate table inner_large_t

/

truncate table inner_small_t

/

 

declare

begin

  for i in 1..50000 loop

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

  end loop;

  for i in 49000..50000 loop

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

  end loop;

  for i in 30000..100000 loop

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

  end loop;

  for i in 1..1000 loop

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

  end loop; 

  commit;

end;

/

 

analyze table outer_large_t compute statistics for table for all indexes

/

analyze table outer_small_t compute statistics for table for all indexes

/

analyze table inner_large_t compute statistics for table for all indexes

/

analyze table inner_small_t compute statistics for table for all indexes

/

 

-- 下面比较各个版本的CBO在执行计划上的不同

-- Oracle 10g

test@GZSERVER> set autotrace on exp

test@GZSERVER> alter session set OPTIMIZER_FEATURES_ENABLE='10.1.0'

  2  /

 

会话已更改。

 

test@GZSERVER> select count(*) from outer_large_t where id in (select id from inner_large_t)

  2  /

 

  COUNT(*)                                                                      

----------                                                                     

     20001                                                                     

 

 

Execution Plan

----------------------------------------------------------                     

   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=278 Card=1 Bytes=2         

          6)                                                                   

   1    0   SORT (AGGREGATE)                                                    

   2    1     HASH JOIN (SEMI) (Cost=278 Card=50000 Bytes=1300000)             

   3    2       INDEX (FAST FULL SCAN) OF 'IDX_OUTER_LARGE_T' (INDEX)          

          (Cost=23 Card=50000 Bytes=650000)                                     

   4    2       INDEX (FAST FULL SCAN) OF 'IDX_INNER_LARGE_T_1' (INDEX         

          ) (Cost=42 Card=70001 Bytes=910013)                                   

 

test@GZSERVER> select count(*) from outer_large_t t1

  2  where exists (select null from inner_large_t t2 where t1.id = t2.id)

  3  /

 

  COUNT(*)                                                                     

----------                                                                     

     20001                                                                      

 

 

Execution Plan

----------------------------------------------------------                     

   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=278 Card=1 Bytes=2         

          6)                                                                    

   1    0   SORT (AGGREGATE)                                                   

   2    1     HASH JOIN (SEMI) (Cost=278 Card=50000 Bytes=1300000)             

   3    2       INDEX (FAST FULL SCAN) OF 'IDX_OUTER_LARGE_T' (INDEX)          

          (Cost=23 Card=50000 Bytes=650000)                                    

   4    2       INDEX (FAST FULL SCAN) OF 'IDX_INNER_LARGE_T_1' (INDEX         

          ) (Cost=42 Card=70001 Bytes=910013)                                   

 

test@GZSERVER> select count(*) from outer_small_t where id in (select id from inner_large_t)

  2  /

 

  COUNT(*)                                                                     

----------                                                                      

      1001                                                                     

 

 

Execution Plan

----------------------------------------------------------                     

   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=45 Card=1 Bytes=26         

          )                                                                    

   1    0   SORT (AGGREGATE)                                                   

   2    1     HASH JOIN (SEMI) (Cost=45 Card=1001 Bytes=26026)                 

   3    2       INDEX (FAST FULL SCAN) OF 'IDX_OUTER_SMALL_T_ID' (INDE         

          X) (Cost=2 Card=1001 Bytes=13013)                                    

   4    2       INDEX (FAST FULL SCAN) OF 'IDX_INNER_LARGE_T_1' (INDEX          

          ) (Cost=42 Card=70001 Bytes=910013)                                   

                                                                               

 

test@GZSERVER> select count(*) from outer_small_t t1

  2  where exists (select null from inner_large_t t2 where t1.id = t2.id)

  3  /

 

  COUNT(*)                                                                     

----------                                                                     

      1001                                                                      

 

 

Execution Plan

----------------------------------------------------------                     

   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=45 Card=1 Bytes=26         

          )                                                                     

   1    0   SORT (AGGREGATE)                                                   

   2    1     HASH JOIN (SEMI) (Cost=45 Card=1001 Bytes=26026)                 

   3    2       INDEX (FAST FULL SCAN) OF 'IDX_OUTER_SMALL_T_ID' (INDE         

          X) (Cost=2 Card=1001 Bytes=13013)                                    

   4    2       INDEX (FAST FULL SCAN) OF 'IDX_INNER_LARGE_T_1' (INDEX         

          ) (Cost=42 Card=70001 Bytes=910013)                                   

 

-- Oracle 9.2.0

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

请登录后发表评论 登录
全部评论
长期从事政务、金融等行业产品研发和架构设计工作,ITPUB数据库版块资深版主,对Oracle、PostgreSQL以及大数据等相关技术有深入研究。现就职于广州云图数据技术有限公司,系统架构师。

注册时间:2007-12-28

  • 博文量
    1252
  • 访问量
    3726940