ITPub博客

首页 > 应用开发 > IT综合 > exists,in,minus的总结,等待补遗

exists,in,minus的总结,等待补遗

原创 IT综合 作者:david3389 时间:2010-07-06 11:42:23 0 删除 编辑
简单测试了一下这三个的特点,总结了一下,也许不太对,等待继续观察和大业务测试。[@more@]

1)exists 和 not exists 用法
select * from a where exists (select * from b where a.id=b.id); 找A表在B表存在的记录
select * from a where not exists (select * from b where a.id=b.id); 找A表不存在于B表的记录

看下执行计划:
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
|* 1 | FILTER | | | | |
| 2 | TABLE ACCESS FULL | A | | | |
|* 3 | TABLE ACCESS FULL | B | | | |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT 0 FROM "B" "B" WHERE "B"."ID"=:B1))
3 - filter("B"."ID"=:B1)

2)minus用法
select * from a minus select * from b; 查找A表中不存在于B表的记录

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | MINUS | | | | |
| 2 | SORT UNIQUE | | | | |
| 3 | TABLE ACCESS FULL | A | | | |
| 4 | SORT UNIQUE | | | | |
| 5 | TABLE ACCESS FULL | B | | | |
--------------------------------------------------------------------

3)in和not in的用法
select * from a where a.id not in (select * from b);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
|* 1 | FILTER | | | | |
| 2 | TABLE ACCESS FULL | A | | | |
|* 3 | TABLE ACCESS FULL | B | | | |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( NOT EXISTS (SELECT 0 FROM "B" "B" WHERE
LNNVL("B"."ID"<>:B1)))
3 - filter(LNNVL("B"."ID"<>:B1))

三种执行计划都列出来了,根据三种执行计划的特性,可以总结出:
1)A数据量小而B数据量非常大时,exists 效率非常高
2)A数据量非常大而B数据量小时,in 效率非常高
3)minus自动去重,他适合两个都是比较小的表。

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

下一篇: ORA-12838的问题
请登录后发表评论 登录
全部评论

注册时间:2008-11-17

  • 博文量
    19
  • 访问量
    120716