ORACLE 9I not exists的bug

wei-xh發表於2012-02-01
Oracle
select * from v$version where rownum=1;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
 
explain plan for
select * from wxh_tbd where object_id not in (select object_id from wxh_tbd1);
---------------------------------------------------------------------
| Id  | Operation             |  Name       | Rows  | Bytes | Cost  |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |   184 | 16744 |    31 |
|*  1 |  HASH JOIN ANTI       |             |   184 | 16744 |    31 |
|   2 |   TABLE ACCESS FULL   | WXH_TBD     | 10970 |   921K|    15 |
|   3 |   INDEX FAST FULL SCAN| T_2         | 21692 |   105K|     4 |
---------------------------------------------------------------------
 
explain plan for
 select * from wxh_tbd where not exists (select 1 from wxh_tbd1 where wxh_tbd1.object_id=wxh_tbd.object_id);
 
--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |   549 | 47214 |   564 |
|*  1 |  FILTER              |             |       |       |       |
|   2 |   TABLE ACCESS FULL  | WXH_TBD     |   549 | 47214 |    15 |
|*  3 |   INDEX RANGE SCAN   | T_2         |     2 |    10 |     1 |
--------------------------------------------------------------------
NOT IN ORACLE做了展開解巢狀，執行計劃走了hash join anti.
但是not exists卻沒有對子查詢進行展開。走了效率比較差的filter操作。
解決辦法，增加HINT是一個比較好的途徑。
explain plan for
 select * from wxh_tbd where not exists (select /*+ unnest */1 from wxh_tbd1 where wxh_tbd1.object_id=wxh_tbd.object_id);
---------------------------------------------------------------------
| Id  | Operation             |  Name       | Rows  | Bytes | Cost  |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |   184 | 16744 |    31 |
|*  1 |  HASH JOIN ANTI       |             |   184 | 16744 |    31 |
|   2 |   TABLE ACCESS FULL   | WXH_TBD     | 10970 |   921K|    15 |
|   3 |   INDEX FAST FULL SCAN| T_2         | 21692 |   105K|     4 |
---------------------------------------------------------------------

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22034023/viewspace-715431/，如需轉載，請註明出處，否則將追究法律責任。

相關文章