oracle in與exists 的區別

表的分佈情況 採用cbo 資料和索引都已分析

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 ptimizer=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 ptimizer=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

目前修改sql有這幾種方式:

1.改成表的連線寫法

select id, role_id, login
      from tb_user e,(Select ID
                      From tb_role f
                    Connect By Prior Id = parent_id
                    Start With Id = 1)d
             where d.id = e.role_id;

2.改成用in的方式 如上所示

3.改成exists 的寫法

SQL> select id, role_id, login
  2    from tb_user e
  3   where Exists (select id
  4            from (Select ID
  5                    From tb_role f
  6                  Connect By Prior Id = parent_id
  7                   Start With Id = 1)
  8           where id = e.role_id);

具體討論,可以參考以下連結: