oracle中not exists对外层查询的影响详解-6ren

oracle中not exists对外层查询的影响详解

转载作者：qq735679552 更新时间：2022-09-29 22:32:09

CFSDN坚持开源创造价值，我们致力于搭建一个资源共享平台，让每一个IT人在这里找到属于你的精彩世界.

这篇CFSDN的博客文章oracle中not exists对外层查询的影响详解由作者收集整理，如果你对这篇文章有兴趣，记得点赞哟.

前言。

最近同事发现了一个问题，在12c中跑的buffer get很高，但是在10g中跑的buffer很低。怀疑是不是12c的优化器有问题.

这个10g的环境和12c的环境，数据量大致一样，只是有很少部分的不同，但是就是这个很少部分不同，造成了not exists中的子查询返回不同的值，进而对外层查询产生不同的影响.

我们来用如下的代码模拟一下.

初始化数据:

 
    ? 
   
 
     
       
       
         --10g 
        
 
         drop 
         table 
         t1; 
        
 
         drop 
         table 
         t2; 
        
 
           
        
 
         create 
         table 
         t1 (id number, 
         name 
         varchar2(20),dep_id varchar2(10)); 
        
 
         create 
         table 
         t2 (id number, 
         name 
         varchar2(20),dep_id varchar2(10)); 
        
 
           
        
 
         insert 
         into 
         t1  
         select 
         rownum, 
         'a' 
         , 
         'kk' 
         from 
         dual  
         connect 
         by 
         level 
         <=3000000; 
        
 
         insert 
         into 
         t2  
         select 
         rownum, 
         'a' 
         , 
         'kk' 
         from 
         dual  
         connect 
         by 
         level 
         <=1000000; 
        
 
         insert 
         into 
         t2  
         select 
         rownum, 
         'a' 
         , 
         'mm' 
         from 
         dual; 
        
 
           
        
 
         commit 
         ; 
        
 
           
        
 
           
        
 
         --12c 
        
 
         drop 
         table 
         t1; 
        
 
         drop 
         table 
         t2; 
        
 
           
        
 
         create 
         table 
         t1 (id number, 
         name 
         varchar2(20),dep_id varchar2(10)); 
        
 
         create 
         table 
         t2 (id number, 
         name 
         varchar2(20),dep_id varchar2(10)); 
        
 
           
        
 
           
        
 
         insert 
         into 
         t1  
         select 
         rownum, 
         'a' 
         , 
         'kk' 
         from 
         dual  
         connect 
         by 
         level 
         <=3000000; 
        
 
         insert 
         into 
         t2  
         select 
         rownum, 
         'a' 
         , 
         'kk' 
         from 
         dual  
         connect 
         by 
         level 
         <=1000000; 
        
 
           
        
 
         commit 
         ; 
        
 
     
 
   

我们看到，12c的数据和10g只是有很少的差别，t1表12c和10g都一样，t2表在12c只是少了一行数据.

 
    ? 
   
         --10g 
        
         SQL>  
         select 
         dep_id, 
         count 
         (*)  
         from 
         t1  
         group 
         by 
         dep_id; 
        
         DEP_ID      
         COUNT 
         (*) 
        
         -------------------- ---------- 
        
         kk      3000000 
        
         SQL>  
         select 
         dep_id, 
         count 
         (*)  
         from 
         t2  
         group 
         by 
         dep_id; 
        
         DEP_ID      
         COUNT 
         (*) 
        
         -------------------- ---------- 
        
         mm       1 
        
         kk      1000000 
        
         SQL> 
        
         --12c 
        
         SQL>  
         select 
         dep_id, 
         count 
         (*)  
         from 
         t1  
         group 
         by 
         dep_id; 
        
         DEP_ID      
         COUNT 
         (*) 
        
         -------------------- ---------- 
        
         kk      3000000 
        
         SQL>  
         select 
         dep_id, 
         count 
         (*)  
         from 
         t2  
         group 
         by 
         dep_id; 
        
         DEP_ID      
         COUNT 
         (*) 
        
         -------------------- ---------- 
        
         kk      1000000 
        
         SQL>

我们将要执行的sql语句是:

 
    ? 
   
         select 
         count 
         (*) 
        
         from 
         t1, t2 
        
         where 
         t1.id = t2.id 
        
         and 
         t1.dep_id =  
         'kk' 
        
         and 
         not 
         exists ( 
         select 
         1 
        
         from 
         t1, t2 
        
         where 
         t1.id = t2.id 
        
         and 
         t2.dep_id =  
         'mm' 
         );

我们先来看执行情况的差距，10g的bufferget小，12c多:

 
    ? 
   
         --10g 
        
         SQL>  
         select 
         /*+ gather_plan_statistics */  
         count 
         (*)  
         from 
         t1,t2  
         where 
         t1.id=t2.id  
         and 
         t1.dep_id= 
         'kk' 
         and 
         not 
         exists ( 
         select 
         1  
         from 
         t1,t2  
         where 
         t1.id=t2.id  
         and 
         t2.dep_id= 
         'mm' 
         ); 
        
         COUNT 
         (*) 
        
         ---------- 
        
         0 
        
         SQL>  
         select 
         *  
         from 
         table 
         (dbms_xplan.display_cursor( 
         null 
         , 
         null 
         , 
         'ALLSTATS LAST' 
         )); 
        
         PLAN_TABLE_OUTPUT 
        
         ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
        
         SQL_ID 22t5mb43w55pr, child number 0 
        
         ------------------------------------- 
        
         select 
         /*+ gather_plan_statistics */  
         count 
         (*)  
         from 
         t1,t2  
         where 
         t1.id=t2.id  
         and 
         t1.dep_id= 
         'kk' 
         and 
         not 
        
         exists ( 
         select 
         1  
         from 
         t1,t2  
         where 
         t1.id=t2.id  
         and 
         t2.dep_id= 
         'mm' 
         ) 
        
         Plan hash value: 3404612428 
        
         ------------------------------------------------------------------------------------------------------------------ 
        
         | Id | Operation   |  
         Name 
         | Starts | E- 
         Rows 
         | A- 
         Rows 
         | A- 
         Time 
         | Buffers | OMem | 1Mem | Used-Mem | 
        
         ------------------------------------------------------------------------------------------------------------------ 
        
         | 0 |  
         SELECT 
         STATEMENT  |  |  1 |  |  1 |00:00:00.02 | 2086 |  |  |   | 
        
         | 1 | SORT AGGREGATE  |  |  1 |  1 |  1 |00:00:00.02 | 2086 |  |  |   | 
        
         |* 2 | FILTER    |  |  1 |  |  0 |00:00:00.02 | 2086 |  |  |   | 
        
         |* 3 | HASH  
         JOIN   
         |  |  0 | 901K|  0 |00:00:00.01 |  0 | 39M| 5518K|   | 
        
         | 4 |   
         TABLE 
         ACCESS  
         FULL 
         | T2 |  0 | 901K|  0 |00:00:00.01 |  0 |  |  |   | 
        
         |* 5 |   
         TABLE 
         ACCESS  
         FULL 
         | T1 |  0 | 2555K|  0 |00:00:00.01 |  0 |  |  |   | 
        
         |* 6 | HASH  
         JOIN   
         |  |  1 |  23 |  1 |00:00:00.02 | 2086 | 1517K| 1517K| 612K (0)| 
        
         |* 7 |   
         TABLE 
         ACCESS  
         FULL 
         | T2 |  1 |  23 |  1 |00:00:00.02 | 2082 |  |  |   | 
        
         | 8 |   
         TABLE 
         ACCESS  
         FULL 
         | T1 |  1 | 2555K|  1 |00:00:00.01 |  4 |  |  |   | 
        
         ------------------------------------------------------------------------------------------------------------------ 
        
         Predicate Information (identified  
         by 
         operation id): 
        
         --------------------------------------------------- 
        
         2 - filter(  
         IS 
         NULL 
         ) 
        
         3 - access( 
         "T1" 
         . 
         "ID" 
         = 
         "T2" 
         . 
         "ID" 
         ) 
        
         5 - filter( 
         "T1" 
         . 
         "DEP_ID" 
         = 
         'kk' 
         ) 
        
         6 - access( 
         "T1" 
         . 
         "ID" 
         = 
         "T2" 
         . 
         "ID" 
         ) 
        
         7 - filter( 
         "T2" 
         . 
         "DEP_ID" 
         = 
         'mm' 
         ) 
        
         Note 
        
         ----- 
        
         -  
         dynamic 
         sampling used  
         for 
         this statement 
        
         34  
         rows 
         selected. 
        
         SQL> 
        
         --12c 
        
         SQL>  
         select 
         /*+ gather_plan_statistics */  
         count 
         (*)  
         from 
         t1,t2  
         where 
         t1.id=t2.id  
         and 
         t1.dep_id= 
         'kk' 
         and 
         not 
         exists ( 
         select 
         1  
         from 
         t1,t2  
         where 
         t1.id=t2.id  
         and 
         t2.dep_id= 
         'mm' 
         ); 
        
         COUNT 
         (*) 
        
         ---------- 
        
         1000000 
        
         SQL>  
         select 
         *  
         from 
         table 
         (dbms_xplan.display_cursor( 
         null 
         , 
         null 
         , 
         'ALLSTATS LAST' 
         )); 
        
         PLAN_TABLE_OUTPUT 
        
         ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 
        
         SQL_ID 22t5mb43w55pr, child number 0 
        
         ------------------------------------- 
        
         select 
         /*+ gather_plan_statistics */  
         count 
         (*)  
         from 
         t1,t2  
         where 
        
         t1.id=t2.id  
         and 
         t1.dep_id= 
         'kk' 
         and 
         not 
         exists ( 
         select 
         1  
         from 
         t1,t2 
        
         where 
         t1.id=t2.id  
         and 
         t2.dep_id= 
         'mm' 
         ) 
        
         Plan hash value: 1692274438 
        
         -------------------------------------------------------------------------------------------------------------------- 
        
         | Id | Operation    |  
         Name 
         | Starts | E- 
         Rows 
         | A- 
         Rows 
         | A- 
         Time 
         | Buffers | OMem | 1Mem | Used-Mem | 
        
         -------------------------------------------------------------------------------------------------------------------- 
        
         | 0 |  
         SELECT 
         STATEMENT  |  |  1 |  |  1 |00:00:00.79 | 10662 |  | |  | 
        
         | 1 | SORT AGGREGATE  |  |  1 |  1 |  1 |00:00:00.79 | 10662 |  | |  | 
        
         |* 2 | FILTER    |  |  1 |  | 1000K|00:00:00.74 | 10662 |  | |  | 
        
         |* 3 | HASH  
         JOIN   
         |  |  1 | 1215K| 1000K|00:00:00.52 | 8579 | 43M| 6111K| 42M (0)| 
        
         | 4 |   
         TABLE 
         ACCESS  
         FULL 
         | T2 |  1 | 1215K| 1000K|00:00:00.01 | 2083 |  | |  | 
        
         |* 5 |   
         TABLE 
         ACCESS  
         FULL 
         | T1 |  1 | 2738K| 3000K|00:00:00.07 | 6496 |  | |  | 
        
         |* 6 | HASH  
         JOIN 
         RIGHT 
         SEMI|  |  1 |  35 |  0 |00:00:00.02 | 2083 | 1245K| 1245K| 461K (0)| 
        
         |* 7 |   
         TABLE 
         ACCESS  
         FULL 
         | T2 |  1 |  23 |  0 |00:00:00.02 | 2083 |  | |  | 
        
         | 8 |   
         TABLE 
         ACCESS  
         FULL 
         | T1 |  0 | 2738K|  0 |00:00:00.01 |  0 |  | |  | 
        
         -------------------------------------------------------------------------------------------------------------------- 
        
         Predicate Information (identified  
         by 
         operation id): 
        
         --------------------------------------------------- 
        
         2 - filter(  
         IS 
         NULL 
         ) 
        
         3 - access( 
         "T1" 
         . 
         "ID" 
         = 
         "T2" 
         . 
         "ID" 
         ) 
        
         5 - filter( 
         "T1" 
         . 
         "DEP_ID" 
         = 
         'kk' 
         ) 
        
         6 - access( 
         "T1" 
         . 
         "ID" 
         = 
         "T2" 
         . 
         "ID" 
         ) 
        
         7 - filter( 
         "T2" 
         . 
         "DEP_ID" 
         = 
         'mm' 
         ) 
        
         Note 
        
         ----- 
        
         -  
         dynamic 
         statistics 
         used:  
         dynamic 
         sampling ( 
         level 
         =2) 
        
         35  
         rows 
         selected. 
        
         SQL> 
        
         SQL>

可以看到第23，24行，在10g中运行时，buffers是0，而在12c中，即78，79行，buffer是2083+6496.

也就是说在10g中，外层查询不进行t1和t2的扫描，直接返回结果了，而在12c中，外层查询还要进行t1表和t2表层扫描才返回结果.

这其实不是10g和12c的差别，而是not exists的返回数据对外层的影响。子查询要返回0行记录，才满足not exist的条件，从而返回外层查询结果.

在10g中，子查询返回了一行记录。

 
    ? 
   
         --10g 
        
         SQL>  
         select 
         1  
         from 
         t1,t2  
         where 
         t1.id=t2.id  
         and 
         t2.dep_id= 
         'mm' 
         ; 
        
         1 
        
         ---------- 
        
         1 
        
         SQL>

不满足not exists（即0行才满足），所以，也就不用在外层继续查询了。直接返回记录0行.

在12c中，子查询返回0行记录，满足not exist的条件，所以还需要在外层查询中继续查询.

 
    ? 
   
         --12c 
        
         SQL>  
         select 
         count 
         (*)  
         from 
         t1,t2  
         where 
         t1.id=t2.id  
         and 
         t2.dep_id= 
         'kk' 
         ; 
        
         COUNT 
         (*) 
        
         ---------- 
        
         1000000 
        
         SQL>  
         set 
         line 1000 
        
         SQL>  
         set 
         pages 1000 
        
         SQL> col PLAN_TABLE_OUTPUT  
         for 
         a250 
        
         SQL> 
        
         SQL> 
        
         SQL>  
         select 
         /*+ gather_plan_statistics */  
         count 
         (*)  
         from 
         t1,t2  
         where 
         t1.id=t2.id  
         and 
         t1.dep_id= 
         'kk' 
         and 
         not 
         exists ( 
         select 
         1  
         from 
         t1,t2  
         where 
         t1.id=t2.id  
         and 
         t2.dep_id= 
         'kk' 
         ); 
        
         COUNT 
         (*) 
        
         ---------- 
        
         0 
        
         SQL>  
         select 
         *  
         from 
         table 
         (dbms_xplan.display_cursor( 
         null 
         , 
         null 
         , 
         'ALLSTATS LAST' 
         )); 
        
         PLAN_TABLE_OUTPUT 
        
         --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
        
         SQL_ID c5hj2p2jt1fxf, child number 0 
        
         ------------------------------------- 
        
         select 
         /*+ gather_plan_statistics */  
         count 
         (*)  
         from 
         t1,t2  
         where 
        
         t1.id=t2.id  
         and 
         t1.dep_id= 
         'kk' 
         and 
         not 
         exists ( 
         select 
         1  
         from 
         t1,t2 
        
         where 
         t1.id=t2.id  
         and 
         t2.dep_id= 
         'kk' 
         ) 
        
         Plan hash value: 1692274438 
        
         -------------------------------------------------------------------------------------------------------------------- 
        
         | Id | Operation    |  
         Name 
         | Starts | E- 
         Rows 
         | A- 
         Rows 
         | A- 
         Time 
         | Buffers | OMem | 1Mem | Used-Mem | 
        
         -------------------------------------------------------------------------------------------------------------------- 
        
         | 0 |  
         SELECT 
         STATEMENT  |  |  1 |  |  1 |00:00:00.28 | 2087 |  | |  | 
        
         | 1 | SORT AGGREGATE  |  |  1 |  1 |  1 |00:00:00.28 | 2087 |  | |  | 
        
         |* 2 | FILTER    |  |  1 |  |  0 |00:00:00.28 | 2087 |  | |  | 
        
         |* 3 | HASH  
         JOIN   
         |  |  0 | 1215K|  0 |00:00:00.01 |  0 | 69M| 7428K|   | 
        
         | 4 |   
         TABLE 
         ACCESS  
         FULL 
         | T2 |  0 | 1215K|  0 |00:00:00.01 |  0 |  | |  | 
        
         |* 5 |   
         TABLE 
         ACCESS  
         FULL 
         | T1 |  0 | 2738K|  0 |00:00:00.01 |  0 |  | |  | 
        
         |* 6 | HASH  
         JOIN 
         RIGHT 
         SEMI|  |  1 | 2738K|  1 |00:00:00.28 | 2087 | 43M| 6111K| 42M (0)| 
        
         |* 7 |   
         TABLE 
         ACCESS  
         FULL 
         | T2 |  1 | 1215K| 1000K|00:00:00.12 | 2083 |  | |  | 
        
         | 8 |   
         TABLE 
         ACCESS  
         FULL 
         | T1 |  1 | 2738K|  1 |00:00:00.01 |  4 |  | |  | 
        
         -------------------------------------------------------------------------------------------------------------------- 
        
         Predicate Information (identified  
         by 
         operation id): 
        
         --------------------------------------------------- 
        
         2 - filter(  
         IS 
         NULL 
         ) 
        
         3 - access( 
         "T1" 
         . 
         "ID" 
         = 
         "T2" 
         . 
         "ID" 
         ) 
        
         5 - filter( 
         "T1" 
         . 
         "DEP_ID" 
         = 
         'kk' 
         ) 
        
         6 - access( 
         "T1" 
         . 
         "ID" 
         = 
         "T2" 
         . 
         "ID" 
         ) 
        
         7 - filter( 
         "T2" 
         . 
         "DEP_ID" 
         = 
         'kk' 
         ) 
        
         Note 
        
         ----- 
        
         -  
         dynamic 
         statistics 
         used:  
         dynamic 
         sampling ( 
         level 
         =2) 
        
         35  
         rows 
         selected. 
        
         SQL>

可以看到第38，39行的buffer为0. 。

总结。

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作能带来一定的帮助，如果有疑问大家可以留言交流.

原文链接：https://oracleblog.org/study-note/not-exists-effect-outer-side-select/ 。

最后此篇关于oracle中not exists对外层查询的影响详解的文章就讲到这里了,如果你想了解更多关于oracle中not exists对外层查询的影响详解的内容请搜索CFSDN的文章或继续浏览相关文章，希望大家以后支持我的博客！。

文章推荐： expdp 中ORA-39002、ORA-39070错误详解及解决办法

文章推荐： Oracle删除archivelog文件的正确方法

文章推荐： oracle 12c创建可插拔数据库（PDB）与用户详解

文章推荐： Oracle 11GR2的递归WITH子查询方法

jstat 详解
全称“Java Virtual Machine statistics monitoring tool”（statistics 统计；monitoring 监控；tool 工具）用于监控虚拟机的各种运
MongoDB索引操作和执行计划Explain()详解
主要是讲下Mongodb的索引的查看、创建、删除、类型说明，还有就是Explain执行计划的解释说明。可以转载，但请注明出处。
Linux下几种并发服务器的实现模式(详解)
1>单线程或者单进程相当于短链接，当accept之后，就开始数据的接收和数据的发送，不接受新的连接，即一个server，一个client 不存在并发。 2>循环服务器和并发服务器
详解 Linux中的关机和重启命令
详解 linux中的关机和重启命令一 shutdown命令 shutdown [选项] 时间选项： ?
用Newtonsoft将json串转为对象的方法(详解)
首先，将json串转为一个JObject对象： ? 1
Python画图常用命令大全(详解)
matplotlib官网 matplotlib库默认英文字体添加黑体（‘SimHei'）为绘图字体代码： plt.rcParams['font.sans-serif']=['SimHei'
Java中的关键字synchronized 详解
在并发编程中，synchronized关键字是常出现的角色。之前我们都称呼synchronized关键字为重量锁，但是在jdk1.6中对synchronized进行了优化，引入了偏向锁、轻量锁。本篇
详解.NET数据库连接池
一般我们的项目中会使用1到2个数据库连接配置，同程艺龙的数据库连接配置被收拢到统一的配置中心，由DBA统一配置和维护，业务方通过某个字符串配置拿到的是Connection对象。
linux下gettimeofday函数windows替换方法(详解)
实例如下： ? 1
详解.Net缓存之MemoryCahe
1. MemoryCahe NetCore中的缓存和System.Runtime.Caching很相似，但是在功能上做了增强，缓存的key支持object类型；提供了泛型支持；可以读缓存和单个缓存
Javascript基础教程之argument 详解
argument是javascript中函数的一个特殊参数，例如下文，利用argument访问函数参数，判断函数是否执行复制代码代码如下: <script
linux中了minerd之后的完全清理过程(详解)
一不小心装了一个Redis服务，开了一个全网的默认端口，一开始以为这台服务器没有公网ip，结果发现之后悔之莫及啊某天发现cpu load高的出奇，发现一个minerd进程占了大量cpu，googl
基于php编程规范(详解)
今天写这个是为了提醒自己编程过程不仅要有逻辑思想还有要规范代码这样可读性 1、PHP 编程规范与编码习惯最主要的有以下几点： 1 文件说明 2 funct
使用yum查看工具lspci所在包并安装的方法(详解)
摘要：虚拟机安装时一般都采用最小化安装，默认没有lspci工具。一台测试虚拟网卡性能的虚拟机，需要lspci工具来查看网卡的类型。本文描述了在一个虚拟机中安装lspci工具的具体步骤。由于要测试
Linux下高并发socket最大连接数所受的各种限制(详解)
1、修改用户进程可打开文件数限制在Linux平台上，无论编写客户端程序还是服务端程序，在进行高并发TCP连接处理时，最高的并发数量都要受到系统对用户单一进程同时可打开文件数量的限制(这是因为系统
一篇文章带你入门java运算符(详解)
目录算术运算符基本四则运算符增量赋值运算符自增/自减运算符关系运算符逻
PHP面向对象之事务脚本模式(详解)
如下所示： ? 1
详解 MapperScannerConfigurer之sqlSessionFactory注入方式
MapperScannerConfigurer之sqlSessionFactory注入方式讲解首先，Mybatis中的有一段配置非常方便，省去我们去写DaoImpl（Dao层实现类）的时间，这个
详解：Linux网络虚拟化技术
Linux的网络虚拟化是LXC项目中的一个子项目，LXC包括文件系统虚拟化，进程空间虚拟化，用户虚拟化，网络虚拟化，等等，这里使用LXC的网络虚拟化来模拟多个网络环境。本文从基本的网络设备讲
highchart数据源纵轴json内的值必须是int(详解)
? 1

qq735679552

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

oracle中not exists对外层查询的影响详解