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

发布时间 - 2026-01-10 23:14:15    点击率：次

前言

最近同事发现了一个问题，在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.

总结

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


# oracle  # not  # exists  # oracle中not  # oracle中的not  # Oracle In和exists not in和not exists的比较分析  # Oracle23ai 新特性IF [NOT] EXISTS语法支持的使  # EXISTS关键字在Oracle中的简单使用例子  # 简述Oracle中in和exists的不同  # oracle中exists和not exists用法举例详解  # 是有  # 而在  # 可以看到  # 也就  # 要在  # 很高  # 少了  # 造成了  # 一个问题  # 这篇文章  # 用在  # 很低  # 还需  # 查询结果  # 执行情况  # 不满足  # 来用  # 发现了  # 也就是说  # 但是在 


相关栏目： 【 网站优化151355 】 【 网络推广146373 】 【 网络技术251813 】 【 AI营销90571 】


相关推荐： 如何在服务器上三步完成建站并提升流量？  Laravel如何使用Scope本地作用域_Laravel模型常用查询逻辑封装技巧【手册】  bootstrap日历插件datetimepicker使用方法  微信小程序 scroll-view组件实现列表页实例代码  rsync同步时出现rsync: failed to set times on “xxxx”: Operation not permitted  BootStrap整体框架之基础布局组件  JavaScript常见的五种数组去重的方式  JS实现鼠标移上去显示图片或微信二维码  如何快速搭建个人网站并优化SEO？  Laravel Admin后台管理框架推荐_Laravel快速开发后台工具  米侠浏览器网页背景异常怎么办 米侠显示修复  悟空识字如何进行跟读录音_悟空识字开启麦克风权限与录音  Laravel怎么使用artisan命令缓存配置和视图  如何在 Go 中优雅地映射具有动态字段的 JSON 对象到结构体  Laravel如何实现全文搜索功能？（Scout和Algolia示例）  网站建设要注意的标准 促进网站用户好感度！  php json中文编码为null的解决办法  网站页面设计需要考虑到这些问题  奇安信“盘古石”团队突破 iOS 26.1 提权  Laravel如何处理异常和错误？（Handler示例）  Claude怎样写约束型提示词_Claude约束提示词写法【教程】  html5如何实现懒加载图片_ intersectionobserver api用法【教程】  免费的流程图制作网站有哪些,2025年教师初级职称申报网上流程？  高端云建站费用究竟需要多少预算？  lovemo网页版地址 lovemo官网手机登录  Laravel怎么实现一对多关联查询_Laravel Eloquent模型关系定义与预加载【实战】  利用 Google AI 进行 YouTube 视频 SEO 描述优化  js实现获取鼠标当前的位置  Edge浏览器提示“由你的组织管理”怎么解决_去除浏览器托管提示【修复】  开心动漫网站制作软件下载,十分开心动画为何停播？  如何在阿里云虚拟机上搭建网站？步骤解析与避坑指南  装修招标网站设计制作流程,装修招标流程？  javascript中的try catch异常捕获机制用法分析  高端企业智能建站程序：SEO优化与响应式模板定制开发  最好的网站制作公司,网购哪个网站口碑最好，推荐几个？谢谢？  如何在阿里云ECS服务器部署织梦CMS网站？  如何在阿里云购买域名并搭建网站？  Laravel如何使用Service Provider注册服务_Laravel服务提供者配置与加载  html如何与html链接_实现多个HTML页面互相链接【互相】  Laravel如何使用集合（Collections）进行数据处理_Laravel Collection常用方法与技巧  Laravel Facade的原理是什么_深入理解Laravel门面及其工作机制  Laravel如何配置任务调度？（Cron Job示例）  Laravel怎么集成Vue.js_Laravel Mix配置Vue开发环境  米侠浏览器网页图片不显示怎么办 米侠图片加载修复  Laravel如何自定义错误页面（404, 500）？（代码示例）  Laravel的.env文件有什么用_Laravel环境变量配置与管理详解  怎样使用JSON进行数据交换_它有什么限制  打造顶配客厅影院，这份100寸电视推荐名单请查收  Laravel怎么判断请求类型_Laravel Request isMethod用法  php后缀怎么变mp4格式错误_修改扩展名提示格式不对怎么办【技巧】 

上一篇：通讯地址能写国外地址吗_通讯地址填写国外地址的格式与注意事项

下一篇：免费云服务器建站：域名绑定与解析设置详解

相关栏目 网站优化
网络推广
网络技术
AI营销
最新文章 Sublime怎么一键压缩JS代码 Su sublime如何在搜索中使用正则表达式 Sublime如何设置透明窗口效果 Su mysql如何设计商品表结构_mysql css属性背景图不显示怎么办_通过检查路 如何使用Golang实现排序_Golan 农历闰月是怎么回事_为合回归年加一月调整 塑造《刺客信条》艾吉奥传奇的编剧离开育碧 1英里等于多少公里 1mile和km的换 css grid布局中行和列是如何定义的 PS批量旋转和翻转图片，快速校正图片方向 C# Swagger UI自定义方法 C OPPO手机九宫格和全键盘怎么切换_OP Go语言如何实现用户登录注册_Golan 1节飞行速度多少公里每小时 1节是多少公 纸嫁衣8千子树第五章庙门怎么开启 庙门 Laravel 多行数据编辑表单中实现逐 明日之后如何提升钓鱼等级 明日之后提升钓 支付宝怎样查年度账单_支付宝年度账单查看 C# 多线程UI更新Dispatcher

上一篇：通讯地址能写国外地址吗_通讯地址填写国外地址的格式与注意事项

下一篇：免费云服务器建站：域名绑定与解析设置详解

©  宿州市腾雀网络科技有限公司 版权所有  皖ICP备2024054708号 

宿州市腾雀网络科技有限公司 宿州市腾雀网络科技有限公司 宿州市腾雀网络科技有限公司 宿州市腾雀网络科技有限公司 宿州市腾雀网络科技有限公司 宿州市腾雀网络科技有限公司 宿州市腾雀网络科技有限公司 宿州市腾雀网络科技有限公司 宿州市腾雀网络科技有限公司 宿州市腾雀网络科技有限公司 宿州市腾雀网络科技有限公司 宿州市腾雀网络科技有限公司 宿州市腾雀网络科技有限公司 宿州市腾雀网络科技有限公司 宿州市腾雀网络科技有限公司 宿州市腾雀网络科技有限公司 宿州市腾雀网络科技有限公司 宿州市腾雀网络科技有限公司