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);