oracle中in和 的区别吗,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);

具体讨论,可以参考以下链接: