in和exists的对比

上一篇 / 下一篇 2008-05-04 17:41:05 / 个人分类：tuning

首先我给出两个等价的SQL：

select id,content from player_message where hasread=5 and end_date>sysdate and id not in (select msg_id from messagemapping t where t.playerid =166);

select * from (select id,content from player_message where hasread=5 and end_date>sysdate) pm where not exists (select msg_id from messagemapping t where pm.id=t.msg_id and t.playerid =166);

一般开发人员可能会用SQL的执行时间长短来衡量语句的优劣，这种想法一般来说是没错的，但在表的数据量不是很大时，执行时间就的衡量就不准确了。下面让我们来看这个两SQL的执行计划和统计信息。

l SQL> select id,content from player_message where hasread=5 and end_date>sysdate and id not in (select msg_id from messagemapping t where t.playerid =166);

Execution Plan

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

0 SELECT STATEMENT ptimizer=CHOOSE (Cost=7 Card=1 Bytes=29)

1 0 FILTER

2 1 TABLE ACCESS (BY INDEX ROWID) OF 'PLAYER_MESSAGE' (Cost=2 Card=1 Bytes=29)

3 2 INDEX (RANGE SCAN) OF 'MESSAGE_END_IDX' (NON-UNIQUE) (Cost=1 Card=1)

4 1 TABLE ACCESS (BY INDEX ROWID) OF 'MESSAGEMAPPING' (Cost=5 Card=1 Bytes=9)

5 4 INDEX (RANGE SCAN) OF 'MSGMAPING_PID_IDX' (NON-UNIQUE) (Cost=3 Card=4)

Statistics

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

0 recursive calls

0 db block gets

49 consistent gets

0 physical reads

0 redo size

531 bytes sent via SQL*Net to client

504 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

l SQL> select * from (select id,content from player_message where hasread=5 and end_date>sysdate) pm where not exists (select msg_id from messagemapping t where pm.id=t.msg_id and t.playerid =166);

Execution Plan

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

0 SELECT STATEMENT ptimizer=CHOOSE (Cost=5 Card=1 Bytes=29)

1 0 FILTER

2 1 TABLE ACCESS (BY INDEX ROWID) OF 'PLAYER_MESSAGE' (Cost=2 Card=1 Bytes=29)

3 2 INDEX (RANGE SCAN) OF 'MESSAGE_END_IDX' (NON-UNIQUE) (Cost=1 Card=1)

4 1 FILTER

5 4 INDEX (RANGE SCAN) OF 'MSGMAPING_PID_IDX' (NON-UNIQUE) (Cost=3 Card=4 Bytes=20)

Statistics

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

0 recursive calls

0 db block gets

2 consistent gets

0 physical reads

0 redo size

531 bytes sent via SQL*Net to client

504 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

由些可看出，带有in的SQL，cost是7，逻辑读是49;带有exists的SQL，cost是5，逻辑读是2。执行次数很少时，两个SQL对系统的开销相差也不是太大，但执行频率增大后，它们之间的开销就很可观了。

下面再通过10046跟踪信息对比：

SQL> alter session set events '10046 trace name context forever,level 12';

SQL> select id,content from player_message where hasread=5 and end_date>sysdate and id not in (select msg_id from messagemapping t where t.playerid =166);

SQL> select * from (select id,content from player_message where hasread=5 and end_date>sysdate) pm where not exists (select msg_id from messagemapping t where pm.id=t.msg_id and t.playerid =166);

SQL> alter session set events '10046 trace name context off';

执行以上SQL，得到trace文件，且通过tkprof格式化后，得到如下片断：

select id,content from

player_message where hasread=5 and end_date>sysdate and id not in (select

msg_id from messagemapping t where t.playerid =166)

Rows Row Source Operation

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

1 FILTER

1 TABLE ACCESS BY INDEX ROWID PLAYER_MESSAGE

1 INDEX RANGE SCAN MESSAGE_END_IDX (object id 34556)

0 TABLE ACCESS BY INDEX ROWID MESSAGEMAPPING

57 INDEX RANGE SCAN MSGMAPING_PID_IDX (object id 33172)