in和exists的区别

认为exists比in效率高的说法是不准确的。
如果查询的两个表大小相当,那么用in和exists差别不大。
如果两个表中一个较小,一个是大表,则子查询表大的用exists,子查询表小的用in

下面是实验:


create table gw4 as select object_id,object_name from dba_objects;
SQL> select * from gw2;


        ID N
---------- -
         1 a
         2 b


2 rows selected.




1.小表做主查询,大表做子查询   结论:如果子表大,则in会转换成exists,走filter连接,主表为驱动表(小表),这时候子查询没有展开。


SQL> select * from gw2 where id in (select object_id from gw4);
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     2 |    30 |     4   (0)| 00:00:01 |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| GW2  |     2 |    30 |     2   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| GW4  |   868 | 11284 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   1 - filter( EXISTS (SELECT 0 FROM "GW4" "GW4" WHERE "OBJECT_ID"=:B1))
   3 - filter("OBJECT_ID"=:B1)




SQL> select * from gw2 where exists (select 0 from gw4 where object_id=gw2.id);
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     2 |    30 |     4   (0)| 00:00:01 |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| GW2  |     2 |    30 |     2   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| GW4  |   868 | 11284 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   1 - filter( EXISTS (SELECT 0 FROM "GW4" "GW4" WHERE "OBJECT_ID"=:B1))
   3 - filter("OBJECT_ID"=:B1)






2.小表做子查询,大表做主查询  结论:如果子表小,则in,走hash连接,子表为驱动表(小表),其实是这个时候进行了子查询展开


SQL> select * from gw4 where object_id in (select id from gw2);                        
---------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          | 86801 |  7798K|   117   (1)| 00:00:02 |
|*  1 |  HASH JOIN RIGHT SEMI|          | 86801 |  7798K|   117   (1)| 00:00:02 |
|   2 |   VIEW               | VW_NSO_1 |     2 |    26 |     2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL | GW2      |     2 |    26 |     2   (0)| 00:00:01 |
|   4 |   TABLE ACCESS FULL  | GW4      | 86801 |  6696K|   114   (0)| 00:00:02 |
---------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   1 - access("OBJECT_ID"="ID")




SQL> select * from gw4 where exists (select 0 from gw2 where id=gw4.object_id);
--------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         | 86801 |  7798K|   117   (1)| 00:00:02 |
|*  1 |  HASH JOIN RIGHT SEMI|         | 86801 |  7798K|   117   (1)| 00:00:02 |
|   2 |   VIEW               | VW_SQ_1 |     2 |    26 |     2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL | GW2     |     2 |    26 |     2   (0)| 00:00:01 |
|   4 |   TABLE ACCESS FULL  | GW4     | 86801 |  6696K|   114   (0)| 00:00:02 |
--------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   1 - access("ITEM_1"="GW4"."OBJECT_ID")


看一下禁止子查询展开的情况:禁止展开,默认oracle改成了exists


SQL> select * from gw4 where object_id in (select /*+no_unnest*/id from gw2);
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    32 |  2528 |  5540   (1)| 00:01:07 |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| GW4  | 86801 |  6696K|   115   (1)| 00:00:02 |
|*  3 |   TABLE ACCESS FULL| GW2  |     1 |    13 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "GW2" "GW2" WHERE
              "ID"=:B1))
   3 - filter("ID"=:B1)




SQL> select * from gw4 where exists (select /*+no_unnest*/0 from gw2 where id=gw4.object_id);
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    32 |  2528 |  5540   (1)| 00:01:07 |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| GW4  | 86801 |  6696K|   115   (1)| 00:00:02 |
|*  3 |   TABLE ACCESS FULL| GW2  |     1 |    13 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "GW2" "GW2" WHERE
              "ID"=:B1))
   3 - filter("ID"=:B1)






总结:


exists:子查询不展开,主表是驱动表。子表大的时候适用。
in:    子查询展开,  子表是驱动表。子表小的时候适用。




简单总结就是:


in     (小表)
exists (大表)
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值