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

最新推荐文章于 2024-10-14 10:03:19 发布
最新推荐文章于 2024-10-14 10:03:19 发布
阅读量3.9k 收藏 2
点赞数
分类专栏： SQL语句学习
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/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.csdn.net/stevendbaguo/article/details/8270572
深圳gg
关注
专栏目录
解决Oracle SQL语句性能问题——SQL语句改写（in、not in、exists及not exists）
数据库生态圈（RDB & NoSQL & Bigdata）——专注于关系库优化（Oracle&Mysql&PG&SQL Server ）
09-10 1015
本文详细说明了解决Oracle SQL性能问题的方法——SQL语句改写（in、not in、exists及not exists），同时，结合实例对其进行了深入讲解。
基于Java的智能家居设计：Java在智能家居能效管理中的应用探究
AGI通用人工智能之禅
09-06 863
基于Java的智能家居设计：Java在智能家居能效管理中的应用探究 作者：禅与计算机程序设计艺术 / Zen and the Art of Computer Programming 1. 背景介绍 1.1 问题的
数据库sql语句的exists总结
fantaxy025025的专栏
08-11 1371
数据库sql语句的exists总结 sql exists in 学习   先来比较下语法： --deals=交易表，areas=地域表，例如香港；我们的目的：查看有交易的地域 select * from areas where id in (select city_id from deals); select * from areas where id in   (select city...
oracle中的exists 和not exists 用法
最新发布
白元霞的博客
10-14 1606
exists （sql 返回结果集为真）not exists (sql 不返回结果集为真）exists 与 in 意思相同，语法不同，效率高于innot exists 与 not in 意思相同，语法不同，效率高于in。
ORACLE IN 与NOT IN 的性能区别
weixin_30675967的博客
02-15 139
业务问题大概可以这样描述，一个父表，一个子表，查询的结果是找到子表中没有使用父表id的记录，这种情况估计很多系统都会牵涉得到。让我们来举一个例子： 　　表一： 父表 parent 　　表二： 子表 childen 　　父表存储父亲，子表存储孩子，然后通过pid和父表关联，查询需要的结果是找到尚未有孩子的父亲。 　　我们来看一下查询语句的写法： 　　select * from p...
oracle 中的exists
weixin_34082789的博客
12-28 267
大家好，第一次发文章。以后会多多记录自己的点滴生活。多积累，多学习。 今天给大家分享一篇自己学的的 oracle 中的 exists 用法，如有遗漏，错误和疏忽。请大家多多包涵，指正。 Where exists 首先，从网上查询了很多资料。意思大致如下。 exists 返回值只是标记或者说一个标识的意思，并不会返回任何的数据。 Exists 是一个判断条件。判断 后续的语句 是否有返回值...
oracle中的exists
Jack Wu
03-23 2070
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 104
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 SQL性能优化之探究.pdf
09-19
使用exists或in有时比not exists或not in更高效。 5. **分组与聚合**：GROUP BY和HAVING子句可能导致大量的数据处理，合理设计分组条件，避免不必要的计算。如果可能，使用索引进行分组。 6. **排序与分页**：...
【Oracle数据库性能提升宝典】：揭秘性能下降的幕后真凶，优化策略大公开
![Oracle数据库]... Oracle数据库性能问题根源探究** Oracle数据库性能问题根源繁多，主要可归纳为以下几个方面： - **硬件瓶颈：**服务器资源不足，如CPU、内存、存储空间等。 -
基于SQL数据库的性能优化问题分析.rar
10-01
3. **避免使用NOT IN和IN运算符**：这些运算符可能导致全表扫描，可以考虑使用EXISTS或NOT EXISTS替换。 二、索引优化 1. **创建合适索引**：为经常出现在WHERE子句中的列创建索引，可以显著提高查询速度。同时，要...
一句话木马拿Shell与菜刀原理
悦分享
07-12 1638
1. 直接上传asp asa jsp cer php aspx htr cdx 格式的木马，不行就加个空格或是利用IIS6.0解析漏洞，常见格式：1.asp;1.jpg 或 1.asp;.jpg 或 1.asp;jpg2. 有时上传图片格式的木马，居然被程序检测拦截了，右键记事本打开木马，在代码最前面加上gif89a，再数据库备份备份成asp格式的木马拿下shell。3. 上传图片格式木马，复制地址到数据库备份备份成asp格式木马，有时不成功的话，就利用IIs6.0解析漏洞，格式：1.asp;
Oracle Exists、 NOT Exists用法
K___End的博客
05-14 3114
EXISTS提供了一种高效的方式来判断行的存在性，是处理子查询时的一个强大工具，尤其是在需要基于条件判断数据是否存在，而不关心具体值的情况下。NOT EXISTS在Oracle数据库中，NOT EXISTS关键字用于子查询中，用来检查子查询是否有结果。与EXISTS相反，NOT EXISTS用于筛选出那些在子查询中找不到匹配项的记录。当子查询没有返回任何行时，NOT EXISTS子句的条件为真，从而外层查询的行会被包含在结果集中。以下是NOT EXISTSNOT EXISTS。
oracle中的 exists 和 in 的效率问题
weixin_33795743的博客
12-16 137
1) select * from T1 where exists(select * from T2 where T1.a=T2.a) ;     T1数据量小而T2数据量非常大时，T1&lt;&lt;T2 时，1) 的查询效率高。 2) select * from T1 where T1.a in (select T2.a from T2) ;      T1数据量非常大而T2数据量小时，T...
oracle中的exists和not exists和in用法详解
cuinijing3404的博客
10-02 448
有两个简单例子，以说明 “exists”和“in”的效率问题 　　1) select * from T1 where exists(select 1 from T2 where T1.a=T2.a) ; 　　T1数据量小而...
ORACLE--EXISTS
u014259295的博客
01-10 337
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 1939
转载: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---EXISTS
qq_41717560的博客
10-09 115
SC表： STUDENT表： SQL语句： 结果： EXISTS (subquery) 如果子查询包含任何行，则EXISTS运算符返回true。 否则它返回false。 1.首先执行SELECT * FROM STUDENT 2.判断EXISTS中的条件是否满足，满足返回true，不满足则返回false 3.为true显示，false不显示 ...
oracle exists
91奔跑的蜗牛
11-25 5393
exists(查询语句) : 存在的意思,判断一张表里面的记录是否存在与另外一张表中。 emp表中的部门号为 10，20，30 部门表中，dept中的部门号为 9，10，20，30，40，50 需求，查询出有员工的部门(查询出存在于emp表中的dept信息) 似乎没有效果，结果查询出的是所有的dept信息。 指定deptno的值，也没效果 select * from dept d1 where e...
oracle not in和not exists的区别
05-18
Oracle中的NOT IN和NOT EXISTS都是用来进行子查询的比较的。它们的主要区别在于： 1. NOT IN使用的是集合的概念，它会将子查询的结果集合与主查询的值进行比较。如果主查询中的某个值不在子查询的结果集中，则返回...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值