ITPub博客

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

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

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

not in & not exists (3-1)


1)        NULL值对结果集的影响

如果子查询返回的结果集存在null值会导致父查询没有结果集返回。

比如查询select * from T1 where x not in ( select y from T2 where z = Z),如果子查询select y from T2 where z = Znull值返回,父查询将得不到任何结果.

test@GZSERVER> update inner_large_t set id = null where rownum <= 1

  2  /

 

已更新 1 行。

 

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

m inner_large_t)

  2  /

 

  COUNT(*)

----------

         0

 

Execution Plan

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

   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1203 Card=1 Bytes=

          13)

   1    0   SORT (AGGREGATE)

   2    1     FILTER

   3    2       TABLE ACCESS (FULL) OF 'OUTER_SMALL_T' (TABLE) (Cost=2

           Card=50 Bytes=650)

   4    2       TABLE ACCESS (FULL) OF 'INNER_LARGE_T' (TABLE) (Cost=4

          8 Card=3500 Bytes=45500)

 

对于父查询中表OUTER_SMALL_T中的某个idx与子查询表INNER_LARGE_T中的每一个idy1,y2,y3…进行比较,如果满足x!=y1 and x!=y2 and x!=y3…x返回到整个查询的结果集中。但因为任何值与null比较均为null,如果子查询存在null的值会导致父查询中没有一个值可以满足条件,故没有结果集返回。

 

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

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

  3  /

 

  COUNT(*)

----------

       501

 

Execution Plan

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

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

          )

 

   1    0   SORT (AGGREGATE)

   2    1     HASH JOIN (ANTI) (Cost=45 Card=1 Bytes=26)

   3    2       TABLE ACCESS (FULL) OF 'OUTER_SMALL_T' (TABLE) (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)

 

对于not exists,由于访问INNER_LARGE_T使用了索引扫描,而索引中是不会包含null值的,可以认为not exists不会理会子查询中null值而直接与非null值作hash反连接。

 

2)        NULL值对执行计划的影响

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

  2  /

 

会话已更改。

 

已用时间:  00: 00: 00.00

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

m inner_large_t)

  2  /

 

  COUNT(*)

----------

       500

 

已用时间:  00: 00: 20.03

 

Execution Plan

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

   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1203 Card=1 Bytes=

          13)

   1    0   SORT (AGGREGATE)

   2    1     FILTER

   3    2       TABLE ACCESS (FULL) OF 'OUTER_SMALL_T' (TABLE) (Cost=2

           Card=50 Bytes=650)

   4    2       TABLE ACCESS (FULL) OF 'INNER_LARGE_T' (TABLE) (Cost=4

          8 Card=3500 Bytes=45500)

 

 

Statistics

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

        328  recursive calls

          0  db block gets

     102703  consistent gets

          0  physical reads

         

 

test@GZSERVER> alter table inner_large_t modify id not null

  2  /

 

表已更改。

 

已用时间:  00: 00: 00.00

test@GZSERVER> alter table outer_small_t modify id not null

  2  /

 

表已更改。

 

已用时间:  00: 00: 00.00

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

m inner_large_t)

  2  /

 

  COUNT(*)

----------

       500

 

已用时间:  00: 00: 00.01

 

Execution Plan

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

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

          )

   1    0   SORT (AGGREGATE)

   2    1     HASH JOIN (ANTI) (Cost=45 Card=1 Bytes=26)

   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)

 

 

Statistics

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

        348  recursive calls

          0  db block gets

        264  consistent gets

          0  physical reads

         

 

我们可以看到,如果outer_small_tinner_large_t的列id没有not null约束的话,执行效率相当低,而加入not null约束后,由于使用了hash反连接,执行效率得到极大的提高:从原来的20.03秒提升为00.01秒,102703的逻辑读减少为264逻辑读。

其实在父、子查询均加入id is not null也是可以达到调整的效果:

test@GZSERVER> select count(*) from outer_small_t

  2  where id is not null and id not in (select id from inner_large_t where id i

s not null)

  3  /

 

  COUNT(*)

----------

       500

 

已用时间:  00: 00: 00.00

 

Execution Plan

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

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

          )

   1    0   SORT (AGGREGATE)

   2    1     HASH JOIN (ANTI) (Cost=45 Card=1 Bytes=26)

   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)

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

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

注册时间:2007-12-28

  • 博文量
    1197
  • 访问量
    3665781