oracle in和exists、not in和not exists原理和性能探究

最新推荐文章于 2025-05-31 19:42:04 发布
最新推荐文章于 2025-05-31 19:42:04 发布
阅读量4k 收藏 2
点赞数
CC 4.0 BY-SA版权
分类专栏： SQL语句学习
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/stevendbaguo/article/details/12854887

      对于in和exists、not in和not exists还是有很多的人有疑惑，更有甚者禁用not in，所有的地方都要用not exists，它真的高效吗？通过下面的使用我们来证明。

先制造一些数据

SQL> drop table test1 purge;
SQL> drop table test2 purge;
SQL> create table test1 as select * from dba_objects where rownum <=1000;
SQL> create table test2 as select * from dba_objects;
SQL> exec dbms_stats.gather_table_stats(user,'test1');
SQL> exec dbms_stats.gather_table_stats(user,'test2');
SQL> set autotrace traceonly

in和exists原理及性能实验：
SQL> select * from test1 t1 where t1.object_id in (select t2.object_id from test2 t2);
已选择1000行。
执行计划
----------------------------------------------------------
Plan hash value: 3819917785
----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |   997 | 84745 |   168   (3)| 00:00:03 |
|*  1 |  HASH JOIN SEMI    |       |   997 | 84745 |   168   (3)| 00:00:03 |
|   2 |   TABLE ACCESS FULL| TEST1 |  1000 | 80000 |     5   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| TEST2 | 50687 |   247K|   162   (2)| 00:00:02 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         95  consistent gets
          0  physical reads
          0  redo size
      45820  bytes sent via SQL*Net to client
       1111  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1000  rows processed

SQL> select *  from test1 t1
  2   where exists (select 1 from test2 t2 where t1.object_id = t2.object_id);
已选择1000行。
执行计划
----------------------------------------------------------
Plan hash value: 3819917785
----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |   997 | 84745 |   168   (3)| 00:00:03 |
|*  1 |  HASH JOIN SEMI    |       |   997 | 84745 |   168   (3)| 00:00:03 |
|   2 |   TABLE ACCESS FULL| TEST1 |  1000 | 80000 |     5   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| TEST2 | 50687 |   247K|   162   (2)| 00:00:02 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         95  consistent gets
          0  physical reads
          0  redo size
      45820  bytes sent via SQL*Net to client
       1111  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1000  rows processed
        结论：在oracle 10g中,in 和 exists其实是一样的，原理就是两张表做HASH JOIN SEMI。也可以通过10053事件看到两条sql语句最终转换成同一条sql。

not in和not exists原理及性能实验：
not exists 比 not in效率高的例子
SQL> select count(*) from test1 where object_id not in(select object_id from test2);
执行计划
----------------------------------------------------------
Plan hash value: 3641219899
-----------------------------------------------------------------------------
| Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |     1 |     4 | 81076   (2)| 00:16:13 |
|   1 |  SORT AGGREGATE     |       |     1 |     4 |            |          |
|*  2 |   FILTER            |       |       |       |            |          |
|   3 |    TABLE ACCESS FULL| TEST1 |  1000 |  4000 |     5   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| TEST2 |     1 |     5 |   162   (2)| 00:00:02 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "TEST2" "TEST2" WHERE
              LNNVL("OBJECT_ID"<>:B1)))
   4 - filter(LNNVL("OBJECT_ID"<>:B1))
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       9410  consistent gets
          0  physical reads
          0  redo size
        407  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


SQL> select count(*) from test1 t1 where not exists
    (select 1 from test2 t2 where t1.object_id=t2.object_id);
执行计划
----------------------------------------------------------
Plan hash value: 240185659
-----------------------------------------------------------------------------
| Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |     1 |     9 |   168   (3)| 00:00:03 |
|   1 |  SORT AGGREGATE     |       |     1 |     9 |            |          |
|*  2 |   HASH JOIN ANTI    |       |     3 |    27 |   168   (3)| 00:00:03 |
|   3 |    TABLE ACCESS FULL| TEST1 |  1000 |  4000 |     5   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| TEST2 | 50687 |   247K|   162   (2)| 00:00:02 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        717  consistent gets
          0  physical reads
          0  redo size
        407  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
          
not in比not exists 效率高的例子
SQL> Set autotrace off
SQL> drop table test1 purge;
表已删除。
SQL> drop table test2 purge;
表已删除。
SQL> create table test1 as select * from dba_objects where rownum <=5;
表已创建。
SQL> create table test2 as select * from dba_objects;
表已创建。
SQL> Insert into test2 select * from dba_objects;
已创建50687行。
SQL> Insert into test2 select * from test2;
已创建101374行。
SQL> Insert into test2 select * from test2;
已创建202748行。
SQL> Commit;
提交完成。
SQL> exec dbms_stats.gather_table_stats(user,'test1');
PL/SQL 过程已成功完成。
SQL> exec dbms_stats.gather_table_stats(user,'test2');
PL/SQL 过程已成功完成。
SQL> Set autotrace traceonly
SQL> select count(*) from test1 where object_id not in(select object_id from test2);
执行计划
----------------------------------------------------------
Plan hash value: 3641219899
-----------------------------------------------------------------------------
| Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |     1 |     3 |  3143   (2)| 00:00:38 |
|   1 |  SORT AGGREGATE     |       |     1 |     3 |            |          |
|*  2 |   FILTER            |       |       |       |            |          |
|   3 |    TABLE ACCESS FULL| TEST1 |     5 |    15 |     3   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| TEST2 |     8 |    40 |  1256   (2)| 00:00:16 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "TEST2" "TEST2" WHERE
              LNNVL("OBJECT_ID"<>:B1)))
   4 - filter(LNNVL("OBJECT_ID"<>:B1))
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         23  consistent gets
          0  physical reads
          0  redo size
        407  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


SQL> select count(*) from test1 t1 where not exists
    (select 1 from test2 t2 where t1.object_id=t2.object_id);
执行计划
----------------------------------------------------------
Plan hash value: 240185659
-----------------------------------------------------------------------------
| Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |     1 |     8 |  1263   (3)| 00:00:16 |
|   1 |  SORT AGGREGATE     |       |     1 |     8 |            |          |
|*  2 |   HASH JOIN ANTI    |       |     1 |     8 |  1263   (3)| 00:00:16 |
|   3 |    TABLE ACCESS FULL| TEST1 |     5 |    15 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| TEST2 |   405K|  1981K|  1253   (2)| 00:00:16 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       5609  consistent gets
          0  physical reads
          0  redo size
        407  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
        结论：not in 和not exists原理是nestedloops 与HASH JOIN的区别，not in中的filter算法类似于nestedloops。如果比较两者的性能，就是比较nestedloops 与HASH JOIN的性能差异。在本例子中：
    not in 性能 大于not exists  test1的数据量5条，test2数量40多万条。
    not exists 性能 大于not in  test1的数据量1000条，test2数量50687条。

not in和not exists还有一个重要区别，就是查询条件后面的语句连接字段中有null值时，not in查询的结果不正确。
http://blog.youkuaiyun.com/stevendbaguo/article/details/8270572
解决Oracle SQL语句性能问题（10.4）——SQL语句改写（3）（in、not in、exists及not exists）
数据库生态圈（RDB & NoSQL & Bigdata）——专注于关系库优化（Oracle&Mysql&PG&SQL Server ）
09-10 1355
本文详细说明了解决Oracle SQL性能问题的方法——SQL语句改写（in、not in、exists及not exists），同时，结合实例对其进行了深入讲解。
ORACLE IN 与NOT IN 的性能区别
weixin_30675967的博客
02-15 163
业务问题大概可以这样描述，一个父表，一个子表，查询的结果是找到子表中没有使用父表id的记录，这种情况估计很多系统都会牵涉得到。让我们来举一个例子： 　　表一： 父表 parent 　　表二： 子表 childen 　　父表存储父亲，子表存储孩子，然后通过pid和父表关联，查询需要的结果是找到尚未有孩子的父亲。 　　我们来看一下查询语句的写法： 　　select * from p...
Oracle中EXISTS & NOT EXISTS的使用
雷神乐乐的博客
05-31 1134
Oracle中EXISTS & NOT EXISTS的使用
oracle 中的exists
weixin_34082789的博客
12-28 304
大家好，第一次发文章。以后会多多记录自己的点滴生活。多积累，多学习。 今天给大家分享一篇自己学的的 oracle 中的 exists 用法，如有遗漏，错误和疏忽。请大家多多包涵，指正。 Where exists 首先，从网上查询了很多资料。意思大致如下。 exists 返回值只是标记或者说一个标识的意思，并不会返回任何的数据。 Exists 是一个判断条件。判断 后续的语句 是否有返回值...
oracle中的exists
Jack Wu
03-23 2117
oracle中的exists不能像MSSQL中似的可以写if exists(),按参考手册所讲:    An EXISTS condition tests for existence of rows in a subquery.只能用在自查询中.例如:   SELECT department_id  FROM departments d  WHERE EXISTS  (SELECT * 
exists和in
liuhuan1534的专栏
05-02 147
in针对in很好理解， Sql代码   select * from T1 where T1.a in (select T2.a from T2) ”      select * from T1 where T1.a in (select T2.a from T2) ”   这里的“in”后面括号里的语句搜索出来的字段的内容一定要相对应，一般来说，T1和T2...
Oracle Exists、 NOT Exists用法
K___End的博客
05-14 4446
EXISTS提供了一种高效的方式来判断行的存在性，是处理子查询时的一个强大工具，尤其是在需要基于条件判断数据是否存在，而不关心具体值的情况下。NOT EXISTS在Oracle数据库中，NOT EXISTS关键字用于子查询中，用来检查子查询是否有结果。与EXISTS相反，NOT EXISTS用于筛选出那些在子查询中找不到匹配项的记录。当子查询没有返回任何行时，NOT EXISTS子句的条件为真，从而外层查询的行会被包含在结果集中。以下是NOT EXISTSNOT EXISTS。
oracle中的exists和not exists和in用法详解
cuinijing3404的博客
10-02 493
有两个简单例子，以说明 “exists”和“in”的效率问题 　　1) select * from T1 where exists(select 1 from T2 where T1.a=T2.a) ; 　　T1数据量小而...
ORACLE--EXISTS
u014259295的博客
01-10 362
1、首先得清除明白EXISTS和NOTEXISTS的在SQL语句中的执行机制顺序（以emp表、mydept表为例进行举例说明） 1）emp查询 SQL> select t.empno,t.ename,t.sal,t.deptno from empt; EMPNO ENAME           SAL DEPTNO ----- ---------- --------- --
Oracle---exists
tanx17的博客
08-21 2040
转载:http://www.cnblogs.com/iliuyuet/p/4784595.html ———————SQL中EXISTS的用法——————————- 比如在Northwind数据库中有一个查询为 SELECT c.CustomerId,CompanyName FROM Customers c WHERE EXISTS( SELECT OrderID FROM Orders
Oracle exist
chinalog的专栏
05-29 1309
Oracle中的EXISTS是用于检查子查询结果是否为空的逻辑运算符
Oracle---EXISTS
qq_41717560的博客
10-09 141
SC表： STUDENT表： SQL语句： 结果： EXISTS (subquery) 如果子查询包含任何行，则EXISTS运算符返回true。 否则它返回false。 1.首先执行SELECT * FROM STUDENT 2.判断EXISTS中的条件是否满足，满足返回true，不满足则返回false 3.为true显示，false不显示 ...
oracle exists
91奔跑的蜗牛
11-25 5540
exists(查询语句) : 存在的意思,判断一张表里面的记录是否存在与另外一张表中。 emp表中的部门号为 10，20，30 部门表中，dept中的部门号为 9，10，20，30，40，50 需求，查询出有员工的部门(查询出存在于emp表中的dept信息) 似乎没有效果，结果查询出的是所有的dept信息。 指定deptno的值，也没效果 select * from dept d1 where e...
oracle中in any和all,Oracle中的Exists、In、ANY、ALL
weixin_39829073的博客
04-11 294
Exists：子查询至少返回一行时条件为true。 Not Exists：子查询不返回任何一行时条件为true。 In：与子查询返回结果集中某个相等。 Not In：与子查询返回结果集中任何一个不相等。 ANY：比子查询返回结果中的某个大。 =ANY：与子查询返回结果中的某个相等。Exists：子查询至少返回一行时条件为true。Not Exists：子查询不返回任何一行时条件为true。In：与...
oracle exists and not exist
yiyu986
08-20 3432
先看下面的例子:oracle中两个系统表.emp,dept.example:1:not exists(not in) not exists:   这条语句返回select * from scott.dept d where e.deptno=d.deptno and d.deptno=10条件满足的结果集.也就是说,返回的结果集中不存在d.deptno=10结果
Oracle Exists与Not Exists 效率问题
samxiaofeng的博客
11-05 1975
oracle NOT exists 效率差 从执行过程来看，LEFT JOIN方式的主要消耗在Sending data一项上，而NOT EXISTS方式主要消耗在executeing和Sending data两项上，受限于Profiling只存放100行记录缘故，从Profiling只能看到47个executeing和Sending data的组合项（每个组合项约50us），通过执行计划看出，外表t_monitor的数据量为578436行，忽略统计信息不准情况下，使用NOT EXISTS方式应该会产生578
oracle 数组 exists,Oracle Exists运算符
weixin_39865277的博客
04-03 666
在本教程中，您将学习如何使用oracle exists运算符来测试行的存在。oracle exists运算符oracle exists运算符是返回true或false的布尔运算符。exists运算符通常与子查询一起使用来测试行的存在：select*fromtable_namewhereexists(subquery);如果子查询返回任何行，则exists运算符返回true，否则返回false。 另...
Oracle中的exists和in
水行云起
09-19 1078
写在前 今天有一个业务，前台是一个供用户选择条件进行查询的功能。选择完毕后点击查询，前台使用get方式发送打包好的参数列表到后台（Controller），持久层拿到参数列表后拼装到SQL中，使用jdbc连接oracle拿数据。 问题 当条件过大（据说有7000多个数据），出现第一个问题，413 FULL HEAD ERROR。很明显是数据量过大，而使用的又是get方式传输，get请求
oracle not exists not in,Oracle exists/in和not exists/not in之前的区别与联系
weixin_36433730的博客
04-03 425
之前写过一篇关于NULL对in和not in结果的影响：Oracle的where条件in/not in中包含NULL时的处理。今天来看看exists和not exists中NULL值对结果的影响。网上经常看到关于in和exixts、not in和not exists性能比对和互换的例子，但它们真的就可以简单互换么？我们通过下面的实验来看一下。实验环境：Oracle 11.2.0.41、创建表并插入...
NOT EXISTS 和 NOT IN 的区别在Oracle
最新发布
06-05
### Oracle 数据库中 NOT EXISTS 与 NOT IN 的区别及使用场景 #### 区别 1. **执行机制** - `NOT IN` 的工作原理是通过比较主查询和子查询中的值来确定是否排除某些记录。如果子查询返回的集合中包含主查询的值，则该记录被排除[^2]。 - `NOT EXISTS` 则通过检查子查询是否返回任何行来决定是否排除记录。如果子查询没有返回任何行，则条件为真，记录被保留[^1]。 2. **处理 NULL 值的能力** - 当子查询可能返回 `NULL` 值时，`NOT IN` 的行为可能会导致意外结果。例如，如果子查询返回了 `NULL`，即使存在匹配的非 `NULL` 值，`NOT IN` 也会将所有记录排除，因为 `NULL` 的逻辑比较会导致不确定的结果[^2]。 - `NOT EXISTS` 不会受到 `NULL` 值的影响，因为它只关注子查询是否返回任何行，而不是具体的值[^1]。 3. **性能差异** - `NOT IN` 在内部实现上通常会进行全表扫描，并且无法有效利用索引，尤其是在子查询返回大量数据或包含 `NULL` 值的情况下[^2]。 - `NOT EXISTS` 通常更高效，因为它可以通过索引快速判断是否存在匹配的行，而无需扫描整个表。此外，`NOT EXISTS` 的执行计划通常涉及嵌套循环（Nested Loop），这在子查询结果较小时尤其有效[^2]。 4. **适用场景** - 如果需要简单地排除特定值集合中的记录，可以考虑使用 `NOT IN`，但前提是确保子查询不会返回 `NULL` 值[^2]。 - 如果需要排除基于复杂条件的记录，或者子查询可能返回 `NULL` 值，推荐使用 `NOT EXISTS`，因为它更加可靠和高效[^1]。 #### 使用示例 ##### 示例 1：使用 `NOT IN` 假设有一个客户表 `customers` 和一个订单表 `orders`，需要查找那些没有任何订单的客户： ```sql SELECT c.customer_id, c.customer_name FROM customers c WHERE c.customer_id NOT IN ( SELECT o.customer_id FROM orders o ); ``` 此查询可能会因为子查询返回 `NULL` 值而导致错误结果。 ##### 示例 2：使用 `NOT EXISTS` 同样的需求可以用 `NOT EXISTS` 实现： ```sql SELECT c.customer_id, c.customer_name FROM customers c WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id ); ``` 此查询不会受到 `NULL` 值的影响，并且通常具有更好的性能。 #### 性能优化建议 - 确保子查询中的列已建立适当的索引，以提高查询效率[^2]。 - 避免在子查询中使用复杂的表达式或函数，这可能会导致性能下降。 #### 注意事项 - 在选择使用 `NOT IN` 或 `NOT EXISTS` 时，需根据具体场景权衡其优缺点。如果子查询可能返回 `NULL` 值或需要更高的性能，优先选择 `NOT EXISTS`。 - `NOT EXISTS` 更适合用于关联子查询，而 `NOT IN` 则适用于简单的值集合比较。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值