ORACLE 9I not exists的bug

最新推荐文章于 2024-03-22 14:34:36 发布

转载最新推荐文章于 2024-03-22 14:34:36 发布 · 144 阅读

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);

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22034023/viewspace-715431/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22034023/viewspace-715431/