oracle in,exists,not in,not exists

最新推荐文章于 2024-09-21 15:54:46 发布
原创 最新推荐文章于 2024-09-21 15:54:46 发布 · 710 阅读 · 0 ·
本内容遵循CC 4.0 BY-SA版权协议
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
收录于
oracle_optimize

当前文章被收录于：

oracle_optimize
65 篇文章 0 人学习
订阅专栏 查看详情
本文探讨了Oracle数据库中使用IN、EXISTS、NOT IN与NOT EXISTS等关键字时的查询优化策略，通过具体案例展示了如何利用这些操作符提升查询性能。
--在in和exists中,oracle可以把父查询推进到子查询


SQL> SELECT COUNT(*)
  2    FROM T0 LT
  3   WHERE  EXISTS (SELECT NULL FROM T1 LA WHERE LA.T1_ID = LT.T0_ID)
  4     AND LT.T0_ID IN (10000,10001,10002,10003,10004,10005,10006,10007,10008,10009,10010,10011,10012,10013,10014,10015,10016);


  COUNT(*)
----------
        17


已用时间:  00: 00: 00.39


执行计划
----------------------------------------------------------
Plan hash value: 2619996249


---------------------------------------------------------------------------------------------------
| Id  | Operation            | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                            |     1 |    10 |    20   (5)| 00:00:01 |
|   1 |  SORT AGGREGATE      |                            |     1 |    10 |            |          |
|   2 |   NESTED LOOPS       |                            |     1 |    10 |    20   (5)| 00:00:01 |
|   3 |    SORT UNIQUE       |                            |  3382 | 16910 |    19   (0)| 00:00:01 |
|   4 |     INLIST ITERATOR  |                            |       |       |            |          |
|*  5 |      INDEX RANGE SCAN| T1_IDX |  3382 | 16910 |    19   (0)| 00:00:01 |
|*  6 |    INDEX UNIQUE SCAN | T0_PK             |     1 |     5 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------


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


   5 - access("LA"."T1_ID"=10000 OR "LA"."T1_ID"=10001 OR
              "LA"."T1_ID"=10002 OR "LA"."T1_ID"=10003 OR
              "LA"."T1_ID"=10004 OR "LA"."T1_ID"=10005 OR
              "LA"."T1_ID"=10006 OR "LA"."T1_ID"=10007 OR
              "LA"."T1_ID"=10008 OR "LA"."T1_ID"=10009 OR
              "LA"."T1_ID"=10010 OR "LA"."T1_ID"=10011 OR
              "LA"."T1_ID"=10012 OR "LA"."T1_ID"=10013 OR
              "LA"."T1_ID"=10014 OR "LA"."T1_ID"=10015 OR
              "LA"."T1_ID"=10016)
   6 - access("LA"."T1_ID"="LT"."T0_ID")
       filter("LT"."T0_ID"=10000 OR "LT"."T0_ID"=10001 OR "LT"."T0_ID"=10002 OR
              "LT"."T0_ID"=10003 OR "LT"."T0_ID"=10004 OR "LT"."T0_ID"=10005 OR
              "LT"."T0_ID"=10006 OR "LT"."T0_ID"=10007 OR "LT"."T0_ID"=10008 OR
              "LT"."T0_ID"=10009 OR "LT"."T0_ID"=10010 OR "LT"."T0_ID"=10011 OR
              "LT"."T0_ID"=10012 OR "LT"."T0_ID"=10013 OR "LT"."T0_ID"=10014 OR
              "LT"."T0_ID"=10015 OR "LT"."T0_ID"=10016)




统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         34  consistent gets
          0  physical reads
          0  redo size
        354  bytes sent via SQL*Net to client
        476  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
 
SQL> SELECT COUNT(*)
  2    FROM T0 LT
  3   WHERE LT.T0_ID IN (SELECT LA.T1_ID FROM T1 LA)
  4     AND LT.T0_ID IN (10000,10001,10002,10003,10004,10005,10006,10007,10008,10009,10010,10011,10012,10013,10014,10015,10016);


  COUNT(*)
----------
        17


已用时间:  00: 00: 00.39


执行计划
----------------------------------------------------------
Plan hash value: 2619996249


---------------------------------------------------------------------------------------------------
| Id  | Operation            | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                            |     1 |    10 |    20   (5)| 00:00:01 |
|   1 |  SORT AGGREGATE      |                            |     1 |    10 |            |          |
|   2 |   NESTED LOOPS       |                            |     1 |    10 |    20   (5)| 00:00:01 |
|   3 |    SORT UNIQUE       |                            |  3382 | 16910 |    19   (0)| 00:00:01 |
|   4 |     INLIST ITERATOR  |                            |       |       |            |          |
|*  5 |      INDEX RANGE SCAN| T1_IDX |  3382 | 16910 |    19   (0)| 00:00:01 |
|*  6 |    INDEX UNIQUE SCAN | T0_PK             |     1 |     5 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------


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


   5 - access("LA"."T1_ID"=10000 OR "LA"."T1_ID"=10001 OR
              "LA"."T1_ID"=10002 OR "LA"."T1_ID"=10003 OR
              "LA"."T1_ID"=10004 OR "LA"."T1_ID"=10005 OR
              "LA"."T1_ID"=10006 OR "LA"."T1_ID"=10007 OR
              "LA"."T1_ID"=10008 OR "LA"."T1_ID"=10009 OR
              "LA"."T1_ID"=10010 OR "LA"."T1_ID"=10011 OR
              "LA"."T1_ID"=10012 OR "LA"."T1_ID"=10013 OR
              "LA"."T1_ID"=10014 OR "LA"."T1_ID"=10015 OR
              "LA"."T1_ID"=10016)
   6 - access("LT"."T0_ID"="LA"."T1_ID")
       filter("LT"."T0_ID"=10000 OR "LT"."T0_ID"=10001 OR "LT"."T0_ID"=10002 OR
              "LT"."T0_ID"=10003 OR "LT"."T0_ID"=10004 OR "LT"."T0_ID"=10005 OR
              "LT"."T0_ID"=10006 OR "LT"."T0_ID"=10007 OR "LT"."T0_ID"=10008 OR
              "LT"."T0_ID"=10009 OR "LT"."T0_ID"=10010 OR "LT"."T0_ID"=10011 OR
              "LT"."T0_ID"=10012 OR "LT"."T0_ID"=10013 OR "LT"."T0_ID"=10014 OR
              "LT"."T0_ID"=10015 OR "LT"."T0_ID"=10016)




统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         34  consistent gets
          0  physical reads
          0  redo size
        354  bytes sent via SQL*Net to client
        476  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed


--从以上查询可知在in,exits,oracle可以把查询条件推进到子查询中,进而可以使用索引  




SQL> SELECT COUNT(*)
  2    FROM T0 LT
  3   WHERE LT.T0_ID NOT IN (SELECT LA.T1_ID FROM T1 LA)
  4     AND LT.T0_ID IN (10000,10001,10002,10003,10004,10005,10006,10007,10008,10009,10010,10011,10012,10013,10014,10015,10016);


  COUNT(*)
----------
         0


已用时间:  00: 00: 01.83


执行计划
----------------------------------------------------------
Plan hash value: 1672357812


----------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name                       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                            |     1 |    10 | 14768   (1)| 00:02:58 |       |       |
|   1 |  SORT AGGREGATE         |                            |     1 |    10 |            |          |       |       |
|*  2 |   FILTER                |                            |       |       |            |          |       |       |
|   3 |    NESTED LOOPS ANTI SNA|                            |     1 |    10 | 14768 (100)| 00:02:58 |       |       |
|   4 |     INLIST ITERATOR     |                            |       |       |            |          |       |       |
|*  5 |      INDEX UNIQUE SCAN  | T0_PK             |    17 |    85 |    15   (0)| 00:00:01 |       |       |
|*  6 |     INDEX RANGE SCAN    | T1_IDX |  7512K|    35M|     2   (0)| 00:00:01 |       |       |
|   7 |    PARTITION RANGE ALL  |                            |     1 |     5 | 14736   (1)| 00:02:57 |     1 |     6 |
|*  8 |     TABLE ACCESS FULL   | T1          |     1 |     5 | 14736   (1)| 00:02:57 |     1 |     6 |
----------------------------------------------------------------------------------------------------------------------


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


   2 - filter( NOT EXISTS (SELECT 0 FROM "T1" "LA" WHERE "LA"."T1_ID" IS NULL))
   5 - access("LT"."T0_ID"=10000 OR "LT"."T0_ID"=10001 OR "LT"."T0_ID"=10002 OR "LT"."T0_ID"=10003 OR
              "LT"."T0_ID"=10004 OR "LT"."T0_ID"=10005 OR "LT"."T0_ID"=10006 OR "LT"."T0_ID"=10007 OR
              "LT"."T0_ID"=10008 OR "LT"."T0_ID"=10009 OR "LT"."T0_ID"=10010 OR "LT"."T0_ID"=10011 OR
              "LT"."T0_ID"=10012 OR "LT"."T0_ID"=10013 OR "LT"."T0_ID"=10014 OR "LT"."T0_ID"=10015 OR
              "LT"."T0_ID"=10016)
   6 - access("LT"."T0_ID"="LA"."T1_ID")
   8 - filter("LA"."T1_ID" IS NULL)




统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      53595  consistent gets
          0  physical reads
          0  redo size
        353  bytes sent via SQL*Net to client
        476  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(*)
  2    FROM T0 LT
  3   WHERE  NOT EXISTS (SELECT NULL FROM T1 LA WHERE LA.T1_ID = LT.T0_ID)
  4     AND LT.T0_ID IN (10000,10001,10002,10003,10004,10005,10006,10007,10008,10009,10010,10011,10012,10013,10014,10015,10016);


  COUNT(*)
----------
         0


已用时间:  00: 00: 00.39


执行计划
----------------------------------------------------------
Plan hash value: 4283638437


---------------------------------------------------------------------------------------------------
| Id  | Operation            | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                            |     1 |    10 |    34   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE      |                            |     1 |    10 |            |          |
|*  2 |   HASH JOIN ANTI     |                            |     1 |    10 |    34   (0)| 00:00:01 |
|   3 |    INLIST ITERATOR   |                            |       |       |            |          |
|*  4 |     INDEX UNIQUE SCAN| T0_PK             |    17 |    85 |    15   (0)| 00:00:01 |
|   5 |    INLIST ITERATOR   |                            |       |       |            |          |
|*  6 |     INDEX RANGE SCAN | T1_IDX |  3382 | 16910 |    19   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------


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


   2 - access("LA"."T1_ID"="LT"."T0_ID")
   4 - access("LT"."T0_ID"=10000 OR "LT"."T0_ID"=10001 OR "LT"."T0_ID"=10002 OR
              "LT"."T0_ID"=10003 OR "LT"."T0_ID"=10004 OR "LT"."T0_ID"=10005 OR
              "LT"."T0_ID"=10006 OR "LT"."T0_ID"=10007 OR "LT"."T0_ID"=10008 OR
              "LT"."T0_ID"=10009 OR "LT"."T0_ID"=10010 OR "LT"."T0_ID"=10011 OR
              "LT"."T0_ID"=10012 OR "LT"."T0_ID"=10013 OR "LT"."T0_ID"=10014 OR
              "LT"."T0_ID"=10015 OR "LT"."T0_ID"=10016)
   6 - access("LA"."T1_ID"=10000 OR "LA"."T1_ID"=10001 OR
              "LA"."T1_ID"=10002 OR "LA"."T1_ID"=10003 OR
              "LA"."T1_ID"=10004 OR "LA"."T1_ID"=10005 OR
              "LA"."T1_ID"=10006 OR "LA"."T1_ID"=10007 OR
              "LA"."T1_ID"=10008 OR "LA"."T1_ID"=10009 OR
              "LA"."T1_ID"=10010 OR "LA"."T1_ID"=10011 OR
              "LA"."T1_ID"=10012 OR "LA"."T1_ID"=10013 OR
              "LA"."T1_ID"=10014 OR "LA"."T1_ID"=10015 OR
              "LA"."T1_ID"=10016)




统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         34  consistent gets
          0  physical reads
          0  redo size
        353  bytes sent via SQL*Net to client
        476  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,oracle不可以把子查询推进到子查询中,进而走了全表扫描
--对于not exists,oracle可以把父查询推进到子查询,进而走索引
--所以如果子查询表很大时not exists较not in好,如果子查询表很少,则反之
oracle 的not in/not exists/in/exists效率对比
u011165335的博客
05-13 4076
前言:没有经过验证的结果都是值得怀疑的 注意:这里的验证,取第二次查询结果 一:先看not in/not exists 这里用scott用户的dept和emp实验 环境oracle 11g, select * from dept d where d.deptno NOT IN (select deptno from emp ) ; select * from dept d wher...
ORACLE中 EXISTS和 NOT EXISTS 的使用，以及EXISTS 和IN ,NOT EXISTS 和 NOT IN 的區別及效率對比
最新发布
宁静致远
09-21 944
总的来说，在大多数情况下，EXISTS和NOT EXISTS因其早期终止特性而更加高效。然而，实际的性能取决于多种因素，包括索引的存在与否、数据分布、表大小等。为了获得最佳性能，建议根据具体情况选择最合适的方法，并考虑使用索引来优化查询。
oracle-not in 和not exists的区别
GreenAppple的专栏
04-20 2294
oracle-not in 和not exists的区别 1.not in 和not exists效率上的区别 在查询语句中使用not in的时候会对内表进行全表扫描，没有用到索引。而not exists的子查询仍然会用到表上的索引。所以无论的大表还是小表，使用not exists都会比使用not in 要快。当然这的前提是表中有索引。 2.not in 和not exists结果上的区别
Oracle中not exists 与not in 的使用情况
清风徐来，水波不兴
06-23 2306
1、在oracle 1、对于not exists查询，内表存在空值对查询结果没有影响；对于notin查询，内表存在空值将导致最终的查询结果为空。 2、对于notexists查询，外表存在空值，存在空值的那条记录最终会输出；对于notin查询，外表存在空值，存在空值的那条记录最终将被过滤，其他数据不受影响。
oracle exists mysql_关于ORACLE MYSQL NOT IN和NOT exists需要注意的 NULL值
weixin_32137855的博客
01-19 274
首先说明NOT IN 和NOT EXISTS 并不完全等价 ORACLE MYSQL 都是如此源表：SQL> select * from testa1;NAME ID-------------------- -----------gaopeng 1gaopeng ...
面试题练习（14）——MySQL与Oracle的“in与exists、not in与not exists”【主要是讲解Oracle的】
風的博客
04-23 1281
一：浅谈sql中的in与not in,exists与not exists的区别【还讲了相应的解决方案】 二：详解not in与not exists的区别与用法（not in的性能并不差） 【浅谈sql中的in与not in,exists与not exists的区别以及性能分析，内容简单地含有一与二】 实例测试： MySQL上：MYSQL中的NOT IN和NOT EXISTS不一样的...
oracle中关于in和exists，not in 和 not exists
cameory的博客
06-16 121
oracle中关于in和exists，not in 和 not exists in和exists in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。 一直以来认为exists比in效率高的说法是不准确的。 如果查询的两个表大小相当，那么用in和exists差别不大。 如果两个表中一个较小，一个是大...
Oracle中的 IN, NOT IN和 EXISTS, NOT EXISTS的區別
weixin_34130269的博客
01-05 99
Oracle中的 IN, NOT IN和 EXISTS, NOT EXISTS的區別 通常聽到的都是說盡量用exists不要用in，因為exists只判斷存在而in需要對比值，所以exists比較快，但看了看網上的一些東西才發現根本不是這麼回事。 下面這段是抄的 Select * from T1 where x in ( select y from T2 ...
Oracle--NOT EXISTS 与 NOT IN 的区别
我妻善逸（あがつまーぜんいつ）的博客
03-03 270
11g之后的oracle版本。 一致的状况：存在性判断的话，不影响结果。 CREATE TABLE students ( ID NUMBER , NAME VARCHAR2(20) ) CREATE TABLE lesson ( ID NUMBER , kc VARCHAR2(20) ) TRUNCATE TABLE students INSERT INTO students VALUES (...
oracle count函数
rudy gao
10-25 3558
1.  count函数 1.1.  count查询结果 count(*)是以所有字段做count count(1)是以查询结果第一个字段做count，两者的结果是一样的 这里的1应该不是位置变量，相当于给*的结果加一个值为1伪列，再count 1的数量 所以结果和count(*)是一样的，即count(rowid) count(1)，你可以理解成有个字段，这个字段就是固定值1，那么也是
表压缩与索引失效
rudy gao
06-21 1930
1. ALTER TABLE … COMPRESS FOR OLTP 此方法对现有数据不压缩，对以后的DML语句相关数据进行OLTP压缩 2. Online Redefinition (DBMS_REDEFINITION) 对现有和以后的数据均压缩。使用DBMS_REDEFINITION可以在线对表进行操作，可以使用并行操作。分区表的global index是个例外，需要在线重定义之后重
oracle清除缓存
rudy gao
10-25 1613
SQL>ALTER SYSTEM FLUSH SHARED_POOL/BUFFER_CACHE/GLOBAL CONTEXT
T​o​m​的​r​u​n​s​t​a​t
rudy gao
06-11 1471
1. 创建临时表
两种分页排序比较
rudy gao
11-29 1286
1.  select a.* from (select t.*, rownum rownum_ from (select * from project c order by c.project_name) t where rownum <= 20) a where rownum_ >= 1; 由执行计划可知，其先进行全表扫描，
oracle函数索引
rudy gao
02-15 920
1. 函数索引能够计算出函数或表达式的值，并将其保存在索引中.用于创建索引的函数不能包含任何聚合函数（ggregate function），如果为用户自定义函 数，则在声明中必须使用 DETERMINISTIC 关键字。 2.要求使用基于成本的优化器.(如果是Choose,则Oracle会自动对分析了的表选择cbo) 3.在自己的模式中的表上创建,须有系统特权query rewrit
执行计划说明
rudy gao
02-23 688
1.　 recursive calls 当执行一条SQL语句时，产生的对其他SQL语句的调用，这些额外的语句称之为''recursive calls''或''recursive SQL statements''. 如： （1）我们做一条insert 时，没有足够的空间来保存row记录，Oracle 通过Recursive Call 来动态的分配空间。 （2）执行DDL语句时，ORACL
Oracle 常见的33个等待事件
rudy gao
07-07 679
一． 等待事件的相关知识：   1.1 等待事件主要可以分为两类，即空闲(IDLE)等待事件和非空闲(NON-IDLE)等待事件。 1). 空闲等待事件指ORACLE正等待某种工作，在诊断和优化数据库的时候，不用过多注意这部分事件。 2). 非空闲等待事件专门针对ORACLE的活动，指数据库任务或应用运行过程中发生的等待，这些等待事件是在调整数据库的时候需要关注与研究的。
监控数据库
rudy gao
05-22 662
1.查看每个会话所占用的内存ddih
OLAP&OLTP
rudy gao
02-13 652
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值