exists和in的区别和优化

最新推荐文章于 2021-08-08 19:30:47 发布
最新推荐文章于 2021-08-08 19:30:47 发布
阅读量824 收藏
点赞数
分类专栏： sql语句
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/rgb_rgb/article/details/11869753
版权 
在oracle中有exists、not exists来进行一些表连接操作，在普通情况下Oracle的执行计划如果使用到semi-join、anti-join方式来进行表连接的话，速度一遍很快，否则很慢。通过使用提示hash_sj hash_aj也可使执行计划走semi-join、anti-join。
下面有两种不同的写法，都会走semi-join
SQL> create table dev.a1 as select * from dba_objects where rownum <= 10000;

Table created.

SQL> create table dev.a2 as 
  2       select * from (select t.*,rownum rd from dba_objects t) p where rd>5000 and rd<= 15000;

Table created.

SQL> create table dev.a3 as 
  2       select * from (select t.*,rownum rd from dba_objects t) p where rd>7500 and rd<= 17500;

Table created.

SQL> analyze table dev.a1 compute statistics;

Table analyzed.

SQL> analyze table dev.a2 compute statistics;

Table analyzed.

SQL> analyze table dev.a3 compute statistics;

Table analyzed.

SQL> set autotrace traceonly;      
SQL> set timing on
SQL> set linesize 177
SQL> /* Formatted on 2013/09/21 14:21:36 (QP5 v5.227.12220.39754) */
SQL>   SELECT a1.owner, COUNT (*)
  2      FROM dev.a1
  3     WHERE    EXISTS
  4                 (SELECT /*+ hash_sj(a1 a2) */
  5                        *
  6                    FROM dev.a2
  7                   WHERE a2.object_id = a1.object_id)
  8           OR EXISTS
  9                 (SELECT /*+ hash_sj(a1 a2) */
 10                        *
 11                    FROM dev.a3
 12                   WHERE a3.object_id = a1.object_id)
 13  GROUP BY a1.owner;

Elapsed: 00:00:00.18

Execution Plan
----------------------------------------------------------
Plan hash value: 3327704572

---------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |     5 |    60 |   124   (2)| 00:00:02 |
|   1 |  HASH GROUP BY        |         |     5 |    60 |   124   (2)| 00:00:02 |
|*  2 |   HASH JOIN SEMI      |         |  5082 | 60984 |   123   (1)| 00:00:02 |
|   3 |    TABLE ACCESS FULL  | A1      | 10000 | 80000 |    40   (0)| 00:00:01 |
|   4 |    VIEW               | VW_SQ_1 | 18422 | 73688 |    82   (0)| 00:00:01 |
|   5 |     UNION-ALL         |         |       |       |            |          |
|   6 |      TABLE ACCESS FULL| A3      |  8422 | 33688 |    38   (0)| 00:00:01 |
|   7 |      TABLE ACCESS FULL| A2      | 10000 | 40000 |    44   (0)| 00:00:01 |
---------------------------------------------------------------------------------

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

   2 - access("VW_COL_1"="A1"."OBJECT_ID")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        406  consistent gets
          0  physical reads
          0  redo size
        709  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed

SQL> 

SQL> /* Formatted on 2013/09/21 14:23:32 (QP5 v5.227.12220.39754) */
SQL>   SELECT x.owner, COUNT (*)
  2      FROM (SELECT *
  3              FROM dev.a1
  4             WHERE EXISTS
  5                      (SELECT /*+ hash_sj(a1 a2)*/
  6                             *
  7                         FROM dev.a2
  8                        WHERE a2.object_id = a1.object_id)
  9            UNION ALL
 10            SELECT *
 11              FROM dev.a1
 12             WHERE     NOT EXISTS
 13                          (SELECT /*+ hash_aj(a1 p)*/
 14                                 *
 15                             FROM dev.a2 p
 16                            WHERE p.object_id = a1.object_id)
 17                   AND EXISTS
 18                          (SELECT /*+ hash_sj(a1 a3)*/
 19                                 *
 20                             FROM dev.a3
 21                            WHERE a3.object_id = a1.object_id)) x
 22  GROUP BY x.owner;

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
Plan hash value: 195072970

---------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |     5 |    25 |   210   (2)| 00:00:03 |
|   1 |  HASH GROUP BY           |      |     5 |    25 |   210   (2)| 00:00:03 |
|   2 |   VIEW                   |      |  5108 | 25540 |   209   (2)| 00:00:03 |
|   3 |    UNION-ALL             |      |       |       |            |          |
|*  4 |     HASH JOIN RIGHT SEMI |      |  5082 | 60984 |    85   (2)| 00:00:02 |
|   5 |      TABLE ACCESS FULL   | A2   | 10000 | 40000 |    44   (0)| 00:00:01 |
|   6 |      TABLE ACCESS FULL   | A1   | 10000 | 80000 |    40   (0)| 00:00:01 |
|*  7 |     HASH JOIN ANTI       |      |    26 |   416 |   124   (2)| 00:00:02 |
|*  8 |      HASH JOIN RIGHT SEMI|      |  2587 | 31044 |    79   (2)| 00:00:01 |
|   9 |       TABLE ACCESS FULL  | A3   |  8422 | 33688 |    38   (0)| 00:00:01 |
|  10 |       TABLE ACCESS FULL  | A1   | 10000 | 80000 |    40   (0)| 00:00:01 |
|  11 |      TABLE ACCESS FULL   | A2   | 10000 | 40000 |    44   (0)| 00:00:01 |
---------------------------------------------------------------------------------

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

   4 - access("A2"."OBJECT_ID"="A1"."OBJECT_ID")
   7 - access("P"."OBJECT_ID"="A1"."OBJECT_ID")
   8 - access("A3"."OBJECT_ID"="A1"."OBJECT_ID")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        686  consistent gets
          0  physical reads
          0  redo size
        709  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed

SQL> 
SQL> SELECT a1.owner, COUNT (*)
  2      FROM dev.a1
  3     WHERE    EXISTS
  4                 (SELECT /*+ hash_sj(a1 a2) */
  5                        *
  6                    FROM dev.a2
  7                   WHERE a2.object_id = a1.object_id)
  8           OR EXISTS
  9                 (SELECT /*+ hash_sj(a1 a2) */
 10                        *
 11                    FROM dev.a3
 12                   WHERE a3.object_id = a1.object_id)
 13  GROUP BY a1.owner;

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
Plan hash value: 3327704572

---------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |     5 |    60 |   124   (2)| 00:00:02 |
|   1 |  HASH GROUP BY        |         |     5 |    60 |   124   (2)| 00:00:02 |
|*  2 |   HASH JOIN SEMI      |         |  5082 | 60984 |   123   (1)| 00:00:02 |
|   3 |    TABLE ACCESS FULL  | A1      | 10000 | 80000 |    40   (0)| 00:00:01 |
|   4 |    VIEW               | VW_SQ_1 | 18422 | 73688 |    82   (0)| 00:00:01 |
|   5 |     UNION-ALL         |         |       |       |            |          |
|   6 |      TABLE ACCESS FULL| A3      |  8422 | 33688 |    38   (0)| 00:00:01 |
|   7 |      TABLE ACCESS FULL| A2      | 10000 | 40000 |    44   (0)| 00:00:01 |
---------------------------------------------------------------------------------

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

   2 - access("VW_COL_1"="A1"."OBJECT_ID")


Statistics
----------------------------------------------------------
         54  recursive calls
          0  db block gets
        424  consistent gets
          1  physical reads
          0  redo size
        709  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          5  rows processed

SQL> 
SQL> 
SQL> /* Formatted on 2013/09/21 14:23:32 (QP5 v5.227.12220.39754) */
SQL>   SELECT x.owner, COUNT (*)
  2      FROM (SELECT *
  3              FROM dev.a1
  4             WHERE EXISTS
  5                      (SELECT /*+ hash_sj(a1 a2)*/
  6                             *
  7                         FROM dev.a2
  8                        WHERE a2.object_id = a1.object_id)
  9            UNION ALL
 10            SELECT *
 11              FROM dev.a1
 12             WHERE     NOT EXISTS
 13                          (SELECT /*+ hash_aj(a1 p)*/
 14                                 *
 15                             FROM dev.a2 p
 16                            WHERE p.object_id = a1.object_id)
 17                   AND EXISTS
 18                          (SELECT /*+ hash_sj(a1 a3)*/
 19                                 *
 20                             FROM dev.a3
 21                            WHERE a3.object_id = a1.object_id)) x
 22  GROUP BY x.owner;

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 195072970

---------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |     5 |    25 |   210   (2)| 00:00:03 |
|   1 |  HASH GROUP BY           |      |     5 |    25 |   210   (2)| 00:00:03 |
|   2 |   VIEW                   |      |  5108 | 25540 |   209   (2)| 00:00:03 |
|   3 |    UNION-ALL             |      |       |       |            |          |
|*  4 |     HASH JOIN RIGHT SEMI |      |  5082 | 60984 |    85   (2)| 00:00:02 |
|   5 |      TABLE ACCESS FULL   | A2   | 10000 | 40000 |    44   (0)| 00:00:01 |
|   6 |      TABLE ACCESS FULL   | A1   | 10000 | 80000 |    40   (0)| 00:00:01 |
|*  7 |     HASH JOIN ANTI       |      |    26 |   416 |   124   (2)| 00:00:02 |
|*  8 |      HASH JOIN RIGHT SEMI|      |  2587 | 31044 |    79   (2)| 00:00:01 |
|   9 |       TABLE ACCESS FULL  | A3   |  8422 | 33688 |    38   (0)| 00:00:01 |
|  10 |       TABLE ACCESS FULL  | A1   | 10000 | 80000 |    40   (0)| 00:00:01 |
|  11 |      TABLE ACCESS FULL   | A2   | 10000 | 40000 |    44   (0)| 00:00:01 |
---------------------------------------------------------------------------------

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

   4 - access("A2"."OBJECT_ID"="A1"."OBJECT_ID")
   7 - access("P"."OBJECT_ID"="A1"."OBJECT_ID")
   8 - access("A3"."OBJECT_ID"="A1"."OBJECT_ID")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        686  consistent gets
          0  physical reads
          0  redo size
        709  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed

SQL> 


如果子句中含有group by这种排序操作,使用in来代替exists操作会大大提高效率,示例如下：
修改一下示例表：
SQL> insert into dev.a2 select * from dev.a2;
SQL> commit;
使用exists操作,无论添加什么提示都不能使其走semi-join,耗费大量时间
SQL> set linesize 177
SQL> set timing on
SQL> set autotrace on
SQL> /* Formatted on 2013/09/21 14:40:50 (QP5 v5.227.12220.39754) */
SQL> SELECT COUNT (*)
  2    FROM dev.a1 a
  3   WHERE EXISTS
  4            (SELECT /*+ hash_sj(a b) */
  5                   *
  6               FROM (  SELECT object_id
  7                         FROM dev.a2
  8                     GROUP BY object_id
  9                       HAVING COUNT (*) > 1) b
 10              WHERE a.object_id = b.object_id);

  COUNT(*)
----------
      5000

Elapsed: 00:00:16.13

Execution Plan
----------------------------------------------------------
Plan hash value: 1410689026

--------------------------------------------------------------------------------
| Id  | Operation               | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |     1 |     3 |   220K  (1)| 00:44:12 |
|   1 |  SORT AGGREGATE         |      |     1 |     3 |            |          |
|*  2 |   FILTER                |      |       |       |            |          |
|   3 |    TABLE ACCESS FULL    | A1   | 10000 | 30000 |    40   (0)| 00:00:01 |
|*  4 |    FILTER               |      |       |       |            |          |
|   5 |     SORT GROUP BY NOSORT|      |     1 |     4 |    44   (0)| 00:00:01 |
|*  6 |      TABLE ACCESS FULL  | A2   |     1 |     4 |    44   (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

   2 - filter( EXISTS (SELECT 0 FROM "DEV"."A2" "A2" WHERE
              "OBJECT_ID"=:B1 GROUP BY "OBJECT_ID" HAVING COUNT(*)>1))
   4 - filter(COUNT(*)>1)
   6 - filter("OBJECT_ID"=:B1)


Statistics
----------------------------------------------------------
          0  recursive calls
      10000  db block gets
    3190133  consistent gets
          0  physical reads
          0  redo size
        526  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> 

将exists改为in句型,就可以用到semi-join,效率大大提高

SQL> /* Formatted on 2013/09/21 14:45:40 (QP5 v5.227.12220.39754) */
SQL> SELECT COUNT (*)
  2    FROM dev.a1 a
  3   WHERE object_id IN (  SELECT object_id
  4                           FROM dev.a2 b
  5                       GROUP BY object_id
  6                         HAVING COUNT (*) > 1);

  COUNT(*)
----------
      5000

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 3434654636

-----------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |     1 |    16 |    86   (3)| 00:00:02 |
|   1 |  SORT AGGREGATE        |          |     1 |    16 |            |          |
|*  2 |   HASH JOIN RIGHT SEMI |          |   500 |  8000 |    86   (3)| 00:00:02 |
|   3 |    VIEW                | VW_NSO_1 |   500 |  6500 |    45   (3)| 00:00:01 |
|*  4 |     FILTER             |          |       |       |            |          |
|   5 |      HASH GROUP BY     |          |    25 |  2000 |    45   (3)| 00:00:01 |
|   6 |       TABLE ACCESS FULL| A2       | 10000 | 40000 |    44   (0)| 00:00:01 |
|   7 |    TABLE ACCESS FULL   | A1       | 10000 | 30000 |    40   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"="OBJECT_ID")
   4 - filter(COUNT(*)>1)


Statistics
----------------------------------------------------------
          2  recursive calls
          1  db block gets
        454  consistent gets
          0  physical reads
          0  redo size
        526  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

rgb_rgb
关注
专栏目录
SQL查询中in和exists的区别分析
12-15
select * from A where id in (select id from B); select * from A where exists (select 1 from B where A.id=B.id); 对于以上两种情况，in是在内存里遍历比较，而exists需要查询数据库，所以当B表数据量较大时，exists效率优于in。 1、select * from A where id in (select id from B); in()只执行一次，它查出B表中的所有id字段并缓存起来。之后，检查A表的id是否与B表中的id相等，如果相等则将A表的记录加入结果集中，直到遍历完A表
In与Exists的区别
L_3890的博客
09-23 132
这两个函数是差不多的，但由于优化方案不同，通常NOT Exists要比NOT IN要快，因为NOT EXISTS可以使用结合算法二NOT IN就不行了，而EXISTS则不如IN快，因为这时候IN可能更多的使用结合算法。 Select * from tableA Where exists(Select * From tableB Where tableB.ID=tableA.ID...
IN与EXISTS优化
我的博客
05-02 400
用inner join，不要用...
in和exists的区别
娜然的专栏
09-07 525
in 是把外表和内表作hash join，而exists是对外表作loop，每次loop再对内表进行查询。 一直以来认为exists比in效率高的说法是不准确的。 如果查询的两个表大小相当，那么用in和exists差别不大。 如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in： create table gw4
遍历数据库表（ACCESS/SQL SERVER）的方法。SQL中IN,NOT IN,EXISTS,NOT EXISTS的用法和差别。数据库中的exists与in
hljqfl的专栏
01-09 2258
遍历数据库表（ACCESS/SQL SERVER）的方法 以前在网上查找遍历SQL数据库表的方法，可以用 select name from sysobjects where xtype='u' and (not name LIKE 'dtproperties')  来查询SQL的系统表（SQL SERVER中系统表记录的系统的一些对象信息，包括表、字段、存储过程等等，其实利用系统表不仅仅可以查...
sql语句优化之用EXISTS替代IN、用NOT EXISTS替代NOT IN的语句
09-14
SQL语句优化之用EXISTS替代IN、用NOT EXISTS替代NOT IN的语句 SQL语句优化是数据库性能优化的重要方面之一。在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接。在这种情况下，使用EXISTS...
MySQL exists 和in 详解及区别
12-16
MySQL中的`EXISTS`和`IN`都是在SQL查询中用来检查某条记录是否符合特定条件的子查询操作符，但它们的工作原理和使用场景有所不同。 `EXISTS`子查询主要检查子查询是否能返回至少一行数据。在这个过程中，子查询的...
如何在ACCESS中如何使用IN /NOT IN,以及更高效的方法
rookiesx的博客
08-16 1万+
DELETE * FROM Sums WHERE ReturnId not in(select ReturnId from Rets); --作用就是删除Sums 表中所有ReturnId 字段的值=Rets 表中returnId字段的值得记录 --可以用表连接 selet A.*  from [Sums] A inner join Rets b on A.ReturnId =B.
access判断表是否存在
new_smile的专栏
12-07 4380
public static class DbConnectionExtensions {     public static bool TableExists(this DbConnection conn, string table)     {         conn.open();         var exists = conn.GetSchema("Tables", new strin
exists 的使用举例
12-20
使用exists 判断ERP系统中单据分录中物料是否符合某种条件，否则不予保存。
sql优化2:db2关于 in (常量...)优化建议
njhart2003的专栏
07-23 3283
 我们在设计程序里，经常碰到需要态组装sql条件的情况，例如，下面是从后台抓取的sql:update MAT_REQPLAN set GATHERFLAG=1 where 1=1and REQPLAN_ID in (select distinct REQPLAN_ID                   from MAT_MATERIAL   where MATERIAL_ID in (0,2
63.网络安全渗透测试—[SQL注入篇2]—[Access+Asp-逐字猜解法注入]
qwsn
08-08 2011
我认为，无论是学习安全还是从事安全的人，多多少少都有些许的情怀和使命感！！！ 文章目录 一、相关概念解释 1、top关键字 2、exists()函数 3、mid()函数 4、asc()函数 5、len()函数 二、Access+Asp 逐字猜解法注入 1、Access+ASP 逐字猜解法注入步骤：`5步` 2、Access+ASP 逐字猜解法示例：
db2 exists 和in联合使用优化方法
热门推荐
v824394795的专栏
04-25 1万+
对于exists和in,当exists里面嵌套in的时候,如果in语句里面只有一个元素的情况下,这时候索引是有效的.如果多于一个元素,这种情况下索引会失效.下面写一个简单的例子来验证以上说法.   sql1: select   1 from pm_measurement_recalculation reca where          exists (
DB2中exists与in的效率对比 — 5秒与21分钟的差距
产品经理，程序人生
11-24 5135
在SQL查询优化中，无论是ORACLE，SQL Server，还是DB2，或者其他数据库产品，都有这样一条优化规则：尽量使用exists去替代查询中的in。 但是关于他们之间的查询效率，并没有给出太多的实际查询对比，都只是看书上写，或者听“牛人”说，就信以为真了； 那么他们之间的效率差距到底有多大呢？我们来看下吧！ 前几天一同事，需要找些数据，然后自己尝试写了这个查询语句，写了一会，写不下去
db2 的优化
suliqiang的专栏
03-10 1821
reorg ,runstats ,reorgchk   1.runstats 收集统计信息， 2.reorgchk       针对系统表进行reorgchk     reorgchk update statistics on table system            针对用户表进行  reorgchk     db2 reorgchk update st
Access函数判断文件是否存在
weixin_33890499的博客
02-12 352
函数名: access 　　头文件：io.h 　　功 能: 确定文件的访问权限，检查某个文件的存取方式，比如说是只读方式、只写方式等。如果指定的存取方式有效，则函数返回0，否则函数返回-1 　　用 法: int access(const char *filename, int amode); 　　int _access(const char *...
exists 和in
最新发布
09-10
exists 和 in 都是用来进行子查询的操作符，但在优化方面存在一些差异。 exists 是一个谓词，它检查子查询是否返回任何结果。如果子查询返回至少一行记录，则 exists 返回 true，否则返回 false。exists 在优化方面...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值