星期一, 4月 23, 2012

[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

vT1 塞多一點資料後 發現比較容易比較出差異性
v T1 資料 遠大於 T2 感受較深
v exists 是每遇到一筆 , 就吐回資料
v in 是先撈取所有資料, 存放在temp tablespace 後, 再做比對.

SQL1: cost 1599
SQL2: cost 1961

沒有留言:

LinkWithin-相關文件

Related Posts Plugin for WordPress, Blogger...