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.

总结

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

(0)

相关推荐

  • Oracle minus用法详解及应用实例

    Oracle minus用法 "minus"直接翻译为中文是"减"的意思,在Oracle中也是用来做减法操作的,只不过它不是传统意义上对数字的减法,而是对查询结果集的减法.A minus B就意味着将结果集A去除结果集B中所包含的所有记录后的结果,即在A中存在,而在B中不存在的记录.其算法跟Java中的Collection的removeAll()类似,即A minus B将只去除A跟B的交集部分,对于B中存在而A中不存在的记录不会做任何操作,也不会抛出异常. Ora

  • Oracle In和exists not in和not exists的比较分析

    把这两个很普遍性的网友比较关心的问题总结回答一下. in和exist的区别 从sql编程角度来说,in直观,exists不直观多一个select, in可以用于各种子查询,而exists好像只用于关联子查询 从性能上来看 exists是用loop的方式,循环的次数影响大,外表要记录数少,内表就无所谓了 in用的是hash join,所以内表如果小,整个查询的范围都会很小,如果内表很大,外表如果也很大就很慢了,这时候exists才真正的会快过in的方式. not in和not exists的区别

  • 简述Oracle中in和exists的不同

    一直以来,大家认为exists比in速度快,其实是不准确的.且看接下来的具体分析:in其实是将外表和内表进行hash join,exists是先对外表进行loop操作,然后每次loop后再对内表进行查询. 如果两张表大小差不多,那么exists和in的效率差不多. 例如: 一张大表为A,一张小表B 一.第一种情况 select * from A where mm in (select mm from B) 效率高,这里用到的是大表A上的索引 select * from B exists (sel

  • Plsql Developer连接Oracle时出现Could not initialize oci.dll解决方案

    用plsql developer连接远程Oracle时会出现Could not initalize oci.dll警告,原因分析:安装完后Oracle的 oci.dll 是64位的,而32位应用程序 PLSQL Developer 无法加载,或者相反.因为之前实习遇到这个问题,我没有做好记录,最近又有同事遇到,问我,然后我就要重新搜索解决这个问题,所以现在就写成博客,做好记录. 步骤: 1.去http://www.oracle.com/technetwork/database/features/

  • linux系统oracle数据库出现ora12505问题的解决方法

    说明: (1)Linux版本 Linux version 2.6.32.12-0.7-default (geeko@buildhost) (gcc version 4.3.4 [gcc-4_3-branch revision 152973] (SUSE Linux) ) #1 SMP 2010-05-20 11:14:20 +0200 1.查看数据库是否已经启动 命令:ps -ef|grep ora 截图: 2.查看监听(要连接到相应用户(su - oracle),例如:oracle) 命令:l

  • 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 v

  • thinkphp中的多表关联查询的实例详解

    thinkphp中的多表关联查询的实例详解 在进行后端管理系统的编程的时候一般会使用框架来进行页面的快速搭建,我最近使用比较多的就是thinkphp框架,thinkphp框架的应用其实就是把前端和后端进行分割管理,前端用户登录查询系统放在thinkphp中的home文件夹中进行管理,后端管理系统放在thinkphp中的admin文件夹中进行管理.对了,在使用thinkphp框架的时候是是要用到mvc架构的,mvc架构就是model(数据模型).view(视图).controller(控制器)的结

  • oracle中函数 trunc(),round(),ceil(),floor的使用详解

    1.round函数(四舍五入) 描述 : 传回一个数值,该数值是按照指定的小数位元数进行四舍五入运算的结果 参数: number : 欲处理之数值 decimal_places : 四舍五入 , 小数取几位 ( 预设为 0 ) select round(123.456, 0) from dual: 返回123 select round(123.456, 1) from dual; 返回123.5 select round(-123.456, 2) from dual; 返回-123.46 2.c

  • oracle中创建序列及序列补零实例详解

    oracle中创建序列及序列补零实例详解 我们经常会在在DB中创建序列: -- Create sequence create sequence COMMON_SEQ minvalue 1 maxvalue 999999999 start with 1 increment by 1 cache 20 cycle; 我们的序列的最小值是从1开始,但是我们想让这种顺序取出来的序列的位数都一样,按照最大数的位数来算,我们需要8位的序列,那么我们就需要在1的前面补上7个零,只需要用下面的方法即可完成 se

  • java 中mongodb的各种操作查询的实例详解

    java 中mongodb的各种操作查询的实例详解 一. 常用查询: 1. 查询一条数据:(多用于保存时判断db中是否已有当前数据,这里 is  精确匹配,模糊匹配 使用regex...) public PageUrl getByUrl(String url) { return findOne(new Query(Criteria.where("url").is(url)),PageUrl.class); } 2. 查询多条数据:linkUrl.id 属于分级查询 public Lis

  • Mybatis中的resultType和resultMap查询操作实例详解

    resultType和resultMap只能有一个成立,resultType是直接表示返回类型的,而resultMap则是对外部ResultMap的引用,resultMap解决复杂查询是的映射问题.比如:列名和对象属性名不一致时可以使用resultMap来配置:还有查询的对象中包含其他的对象等. MyBatisConfig.xml <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configura

  • Oracle数据库表被锁如何查询和解锁详解

    目录 1.锁表原因 2.锁表查询的代码有以下的形式 3.查看哪个表被锁 4.查看是哪个session引起的 5.杀掉对应进程 6.如何避免锁表 总结 1.锁表原因 可能是修改表中的数据,忘了提交事务会造成锁表. Oracle数据库操作中,我们有时会用到锁表查询以及解锁和kill进程等操作. 2.锁表查询的代码有以下的形式 select count(*) from v$locked_object; select * from v$locked_object; 3.查看哪个表被锁 select b.

  • oracle中if/else的三种实现方式详解

    1.标准sql规范 1.单个IF IF v=... THEN END IF; 2.IF ... ELSE IF v=... THEN ELSE t....; END IF; 3.多个IF IF v=... THEN ELSIF v=... THEN t...; END IFL 注意: 多个IF的是'ELSIF' 不是 ' ELSE IF' 2.decode函数 DECODE(VALUE,IF1,THEN1,IF2,THEN2,IF2,THEN2,..,ELSE) 表示如果value等于if1时,

  • ORACLE中如何找到未提交事务的SQL语句详解

    在Oracle数据库中,我们能否找到未提交事务(uncommit transactin)的SQL语句或其他相关信息呢? 关于这个问题,我们先来看看实验测试吧.实践出真知. 首先,我们在会话1(SID=63)中构造一个未提交的事务,如下所: SQL> create table test 2 as 3 select * from dba_objects; Table created. SQL> select userenv('sid') from dual; USERENV('SID') ----

  • Android 7.0中新签名对多渠道打包的影响详解

    老签名多渠道打包原理 前言 由于Android7.0发布了新的签名机制,加强了签名的加固,导致在新的签名机制下无法通过美团式的方式再继续打多渠道包了.不过在说新的签名机制对打包方案的 影响和为什么会影响我们原有的打包机制之前,需要先简单理解下打包原理和签名在整个打包过程中的作用. Android打包流程 Android打包过程大致如图所示,整个流程就是将Java代码,资源文件以及第三方库整合成一个Apk文件,并对整合后的文件进行签名和优化对齐.整个过程可以简 单分为以下几个步骤: 资源预编译 为

随机推荐