[SQL] in 與 exists 的效能比較

測試環境為 Oracle 10g DB

建立相關表格 tb1 , tb2

SQL>

create table test.tb1 as select * from dba_objects;

create table test.tb2 as select * from dba_objects;

SQL1:

set autotrace traceonly exp stat

select object_name from test.tb1 T1 where exists (select object_name from test.tb2 T2 where T1.owner=T2.owner);

Execution Plan

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

Plan hash value: 1478961495

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

| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT    |      | 35628 |  3479K|   639   (3)| 00:00:08 |

|*  1 |  HASH JOIN          |      | 35628 |  3479K|   639   (3)| 00:00:08 |

|   2 |   SORT UNIQUE       |      | 55040 |   913K|   163   (2)| 00:00:02 |

|   3 |    TABLE ACCESS FULL| TB2  | 55040 |   913K|   163   (2)| 00:00:02 |

|   4 |   TABLE ACCESS FULL | TB1  | 55422 |  4492K|   163   (2)| 00:00:02 |

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

SQL2:

set autotrace traceonly exp stat

select object_name from test.tb1 T1 where T1.object_name in (select object_name from test.tb2 T2);

Execution Plan

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

Plan hash value: 1669325194

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

-----

| Id  | Operation            | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time

    |

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

-----

|   0 | SELECT STATEMENT     |      | 50011 |  6446K|       |   738   (2)| 00:00

:09 |

|*  1 |  HASH JOIN RIGHT SEMI|      | 50011 |  6446K|  4200K|   738   (2)| 00:00

:09 |

|   2 |   TABLE ACCESS FULL  | TB2  | 55040 |  3547K|       |   163   (2)| 00:00

:02 |

|   3 |   TABLE ACCESS FULL  | TB1  | 55422 |  3572K|       |   163   (2)| 00:00

:02 |

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

-----

insert into test.tb1 select * from dba_objects;

insert into test.tb1 select * from dba_objects;

insert into test.tb1 select * from dba_objects;

insert into test.tb1 select * from dba_objects;

insert into test.tb1 select * from dba_objects;

insert into test.tb1 select * from dba_objects;

insert into test.tb1 select * from dba_objects;

insert into test.tb1 select * from dba_objects;

commit;

analyze table test.tb1 compute statistics;

analyze table test.tb2 compute statistics;

SQL>

set autotrace off

col segment_name for a15

select segment_name , bytes/1024/1024 MB from dba_segments where segment_name in ('TB1','TB2') and owner='TEST';

SEGMENT_NAME            MB

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

TB1                     51

TB2                      6

v 當T1 塞多一點資料後 發現比較容易比較出差異性

v T1 資料 遠大於 T2 時 感受較深

v exists 是每遇到一筆 , 就吐回資料

v in 是先撈取所有資料, 存放在temp tablespace 後, 再做比對.

SQL1: cost 1599

SQL2: cost 1961