熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

oracle in和exists、not in和not exists原理和性能探究

2013-11-13 16:00:20  來源: Oracle 

  對於in和existsnot in和not exists還是有很多的人有疑惑更有甚者禁用not in所有的地方都要用not exists它真的高效嗎?通過下面的使用我們來證明

  先制造一些數據

  SQL> drop table test purge;

  SQL> drop table test purge;

  SQL> create table test as select * from dba_objects where rownum <=;

  SQL> create table test as select * from dba_objects;

  SQL> exec dbms_statsgather_table_stats(usertest

  SQL> exec dbms_statsgather_table_stats(usertest

  SQL> set autotrace traceonly

  in和exists原理及性能實驗

  SQL> select * from test t where tobject_id in (select tobject_id from test t

  已選擇

  執行計劃

  

  Plan hash value:

  

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

  

  |   | SELECT STATEMENT   |       |   | |      ()| :: |

  |*  |  HASH JOIN SEMI    |       |   | |      ()| :: |

  |   |   TABLE ACCESS FULL| TEST | |        ()| :: |

  |   |   TABLE ACCESS FULL| TEST | |   K|      ()| :: |

  

  Predicate Information (identified by operation id)

  

   access(TOBJECT_ID=TOBJECT_ID

  統計信息

  

    recursive calls

    db block gets

    consistent gets

    physical reads

    redo size

    bytes sent via SQL*Net to client

    bytes received via SQL*Net from client

    SQL*Net roundtrips to/from client

    sorts (memory)

    sorts (disk)

    rows processed

  SQL> select *  from test t

     where exists (select from test t where tobject_id = tobject_id)

  已選擇

  執行計劃

  

  Plan hash value:

  

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

  

  |   | SELECT STATEMENT   |       |   | |      ()| :: |

  |*  |  HASH JOIN SEMI    |       |   | |      ()| :: |

  |   |   TABLE ACCESS FULL| TEST | |        ()| :: |

  |   |   TABLE ACCESS FULL| TEST | |   K|      ()| :: |

  

  Predicate Information (identified by operation id)

  

   access(TOBJECT_ID=TOBJECT_ID

  統計信息

  

    recursive calls

    db block gets

    consistent gets

    physical reads

    redo size

    bytes sent via SQL*Net to client

    bytes received via SQL*Net from client

    SQL*Net roundtrips to/from client

    sorts (memory)

    sorts (disk)

    rows processed

  結論在oracle g中in 和 exists其實是一樣的原理就是兩張表做HASH JOIN SEMI也可以通過事件看到兩條sql語句最終轉換成同一條sql

  not in和not exists原理及性能實驗

  not exists 比 not in效率高的例子

  SQL> select count(*) from test where object_id not in(select object_id from test

  執行計劃

  

  Plan hash value:

  

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

  

  |   | SELECT STATEMENT    |       |     |     |    ()| :: |

  |   |  SORT AGGREGATE     |       |     |     |            |          |

  |*  |   FILTER            |       |       |       |            |          |

  |   |    TABLE ACCESS FULL| TEST |        ()| :: |

  |*  |    TABLE ACCESS FULL| TEST |     |     |      ()| :: |

  

  Predicate Information (identified by operation id)

  

   filter( NOT EXISTS (SELECT /*+ */ FROM TEST TEST WHERE

  LNNVL(OBJECT_ID<>:B)))

   filter(LNNVL(OBJECT_ID<>:B))

  統計信息

  

    recursive calls

    db block gets

    consistent gets

    physical reads

    redo size

    bytes sent via SQL*Net to client

    bytes received via SQL*Net from client

    SQL*Net roundtrips to/from client

    sorts (memory)

    sorts (disk)

    rows processed

  SQL> select count(*) from test t where not exists

  (select from test t where tobject_id=tobject_id)

  執行計劃

  

  Plan hash value:

  

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

  

  |   | SELECT STATEMENT    |       |     |     |      ()| :: |

  |   |  SORT AGGREGATE     |       |     |     |            |          |

  |*  |   HASH JOIN ANTI    |       |     |    |      ()| :: |

  |   |    TABLE ACCESS FULL| TEST |        ()| :: |

  |   |    TABLE ACCESS FULL| TEST | |   K|      ()| :: |

  

  Predicate Information (identified by operation id)

  

   access(TOBJECT_ID=TOBJECT_ID

  統計信息

  

    recursive calls

    db block gets

    consistent gets

    physical reads

    redo size

    bytes sent via SQL*Net to client

    bytes received via SQL*Net from client

    SQL*Net roundtrips to/from client

    sorts (memory)

    sorts (disk)

    rows processed

  not in比not exists 效率高的例子

  SQL> Set autotrace off

  SQL> drop table test purge;

  表已刪除

  SQL> drop table test purge;

  表已刪除

  SQL> create table test as select * from dba_objects where rownum <=;

  表已創建

  SQL> create table test as select * from dba_objects;

  表已創建

  SQL> Insert into test select * from dba_objects;

  已創建

  SQL> Insert into test select * from test;

  已創建

  SQL> Insert into test select * from test;

  已創建

  SQL> Commit;

  提交完成

  SQL> exec dbms_statsgather_table_stats(usertest

  PL/SQL 過程已成功完成

  SQL> exec dbms_statsgather_table_stats(usertest

  PL/SQL 過程已成功完成

  SQL> Set autotrace traceonly

  SQL> select count(*) from test where object_id not in(select object_id from test

  執行計劃

  

  Plan hash value:

  

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

  

  |   | SELECT STATEMENT    |       |     |        ()| :: |

  |   |  SORT AGGREGATE     |       |     |     |            |          |

  |*  |   FILTER            |       |       |       |            |          |

  |   |    TABLE ACCESS FULL| TEST |     |    |        ()| :: |

  |*  |    TABLE ACCESS FULL| TEST |     |       ()| :: |

  

  Predicate Information (identified by operation id)

  

   filter( NOT EXISTS (SELECT /*+ */ FROM TEST TEST WHERE

  LNNVL(OBJECT_ID<>:B)))

   filter(LNNVL(OBJECT_ID<>:B))

  統計信息

  

    recursive calls

    db block gets

    consistent gets

    physical reads

    redo size

    bytes sent via SQL*Net to client

    bytes received via SQL*Net from client

    SQL*Net roundtrips to/from client

    sorts (memory)

    sorts (disk)

    rows processed

  SQL> select count(*) from test t where not exists

  (select from test t where tobject_id=tobject_id)

  執行計劃

  

  Plan hash value:

  

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

  

  |   | SELECT STATEMENT    |       |     |        ()| :: |

  |   |  SORT AGGREGATE     |       |     |     |            |          |

  |*  |   HASH JOIN ANTI    |       |     |        ()| :: |

  |   |    TABLE ACCESS FULL| TEST |     |    |        ()| :: |

  |   |    TABLE ACCESS FULL| TEST |   K|  K|     ()| :: |

  

  Predicate Information (identified by operation id)

  

   access(TOBJECT_ID=TOBJECT_ID

  統計信息

  

    recursive calls

    db block gets

    consistent gets

    physical reads

    redo size

    bytes sent via SQL*Net to client

    bytes received via SQL*Net from client

    SQL*Net roundtrips to/from client

    sorts (memory)

    sorts (disk)

    rows processed

  結論not in 和not exists原理是nestedloops 與HASH JOIN的區別not in中的filter算法類似於nestedloops如果比較兩者的性能就是比較nestedloops 與HASH JOIN的性能差異在本例子中

  not in 性能 大於not exists  test的數據量test數量多萬條

  not exists 性能 大於not in  test的數據量test數量


From:http://tw.wingwit.com/Article/program/Oracle/201311/17657.html
  • 上一篇文章:

  • 下一篇文章:
  • Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.