请教in和exists的区别

paulyibinyi

表的分布情况 采用cbo 数据和索引都已分析 ,麻烦讲下这in和exists两种方式的区别,为什么相差这么大?
tb_role 为8960行  建有parent_id的索引
tb_user 为8541行 建有role_id的索引
SQL> select id, role_id, login
  2 from tb_user e
  3 where Exists (Select ID
  4 From tb_role f
  5 where e.role_id = f.id
  6 Connect By Prior Id = parent_id
  7 Start With Id = 1);

8369 rows selected.


Execution Plan
----------------------------------------------------------
   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1292 Card=428 Bytes=
          9416)

   1 0 FILTER
   2 1 TABLE ACCESS (FULL) OF 'tb_user' (Cost=8 Card=428 Byte
          s=9416)

   3 1 FILTER
   4 3 CONNECT BY (WITH FILTERING)
   5 4 NESTED LOOPS
   6 5 INDEX (UNIQUE SCAN) OF 'PK_tb_role' (UNIQUE)
           (Cost=1 Card=1 Bytes=5)

   7 5 TABLE ACCESS (BY USER ROWID) OF 'tb_role'
   8 4 NESTED LOOPS
   9 8 BUFFER (SORT)
  10 9 CONNECT BY PUMP
  11 8 TABLE ACCESS (BY INDEX ROWID) OF 'tb_role' (
          Cost=3 Card=18 Bytes=162)

  12 11 INDEX (RANGE SCAN) OF 'INX_tb_role_PARENT_
          ID' (NON-UNIQUE) (Cost=1 Card=18)





Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
   84313553 consistent gets
          0 physical reads
          0 redo size
     224163 bytes sent via SQL*Net to client
       6553 bytes received via SQL*Net from client
        559 SQL*Net roundtrips to/from client
     142392 sorts (memory)
          0 sorts (disk)
       8369 rows processed

SQL>
一致性读达到 84313553 consistent gets ,而造成数据很慢查出来

改用in的方式 一致性读马上降低到10250  ,马上查出数据
SQL> select id, role_id, login
  2 from tb_user e
  3 where role_id in (Select ID
  4 From tb_role f
  5 Connect By Prior Id = parent_id
  6 Start With Id = 1);

8369 rows selected.


Execution Plan
----------------------------------------------------------
   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=14 Card=18 Bytes=630
          )

   1 0 HASH JOIN (SEMI) (Cost=14 Card=18 Bytes=630)
   2 1 TABLE ACCESS (FULL) OF 'tb_user' (Cost=8 Card=8556 Byt
          es=188232)

   3 1 VIEW OF 'VW_NSO_1' (Cost=3 Card=18 Bytes=234)
   4 3 CONNECT BY (WITH FILTERING)
   5 4 NESTED LOOPS
   6 5 INDEX (UNIQUE SCAN) OF 'PK_tb_role' (UNIQUE)
           (Cost=1 Card=1 Bytes=5)

   7 5 TABLE ACCESS (BY USER ROWID) OF 'tb_role'
   8 4 NESTED LOOPS
   9 8 BUFFER (SORT)
  10 9 CONNECT BY PUMP
  11 8 TABLE ACCESS (BY INDEX ROWID) OF 'tb_role' (
          Cost=3 Card=18 Bytes=162)

  12 11 INDEX (RANGE SCAN) OF 'INX_tb_role_PARENT_
          ID' (NON-UNIQUE) (Cost=1 Card=18)





Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
      10250 consistent gets          
          0 physical reads
          0 redo size
     224163 bytes sent via SQL*Net to client
       6553 bytes received via SQL*Net from client
        559 SQL*Net roundtrips to/from client
         17 sorts (memory)
          0 sorts (disk)
       8369 rows processed

paulyibinyi

exists 用到了nested loops
in用上了 hash join

weilaiyxj

In和exists使用及性能分析(三)：in和exists的性能分析
http://space6212.itpub.net/post/12157/242077

paulyibinyi

原帖由 weilaiyxj 于 2008-4-14 11:22 发表
In和exists使用及性能分析(三)：in和exists的性能分析
http://space6212.itpub.net/post/12157/242077

谢谢,还是没找到答案 现在两个表数据量差不多,为什么用in 比 exists效率好那么多  9.2.0.4  cbo 模式下

anlinew

原帖由 paulyibinyi 于 2008-4-14 11:15 发表
exists 用到了nested loops
in用上了 hash join

exists用的filter

microsoft_fly

原帖由 paulyibinyi 于 2008-4-14 11:15 发表
exists 用到了nested loops
in用上了 hash join

in 里面多了个 VIEW OF 'VW_NSO_1' 与tb_user hash_join,而exists里不存在这个VIEW,而是作为filter,但没想到有这么大的consistents gets!

paulyibinyi

原帖由 anlinew 于 2008-4-14 11:39 发表

exists用的filter

tks,为什么区别这么大

shiri512003

E:study resB19306_01server.10214211sql_1016.htm#i30971
In certain circumstances, it is better to use IN rather than EXISTS. In general, if the selective predicate is in the subquery, then use IN. If the selective predicate is in the parent query, then use EXISTS.

oracle文档上有详细的描述的，可以参考下，并且有例子的

paulyibinyi

原帖由 microsoft_fly 于 2008-4-14 11:40 发表

in 里面多了个 VIEW OF 'VW_NSO_1' 与tb_user hash_join,而exists里不存在这个VIEW,而是作为filter,但没想到有这么大的consistents gets!

谢谢 分析的很仔细

anlinew

原帖由 shiri512003 于 2008-4-14 11:44 发表
E:study resB19306_01server.10214211sql_1016.htm#i30971
In certain circumstances, it is better to use IN rather than EXISTS. In general, if the selective predicate is in the subquery, then use IN. If the selective predicate is in the parent query, then use EXISTS.

oracle文档上有详细的描述的，可以参考下，并且有例子的

这个例子不太符合这个role