子查询中的filter剖析

云晨姑娘_4054

云晨姑娘_4054

2016-06-07

1359人浏览

原创

在oracle 8i时候,子查询展开为表连接有很多的限制,所以经常我们会听见in和exist的写法会影响sql的性能,会看见很多文章提到一个论点是:in操作是优先对子查询做操作,然后驱动外部的查询,exists则是优先进行外部的查询,然后去驱动子查询, 这个结论真的

在oracle 8i时候,子查询展开为表连接有很多的限制,所以经常我们会听见in和exist的写法会影响sql的性能,会看见很多文章提到一个论点是:in操作是优先对子查询做操作,然后驱动外部的查询,exists则是优先进行外部的查询,然后去驱动子查询,

这个结论真的正确吗,我们来看看如果我们关闭子查询展开为表连接的特性,再来看看是否真的如上述所述:
SQL> create table xiaoyu03 as select * from dba_objects;
SQL> create table xiaoyu04 as select * from dba_objects where rownum SQL> explain plan for select /*+optimizer_features_enable('8.1.7')*/* from xiaoyu04 a where a.object_id in (select /*+no_unnest*/object_id from xiaoyu03 b where b.object_type='TABLE');

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2861815236

---------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost  |
---------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |    16 |  3312 |     1 |
|*  1 |  FILTER            |          |       |       |       |
|   2 |   TABLE ACCESS FULL| XIAOYU04 |    16 |  3312 |     1 |
|*  3 |   TABLE ACCESS FULL| XIAOYU03 |     1 |    11 |    24 |
---------------------------------------------------------------

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

   1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "XIAOYU03" "B"
              WHERE "OBJECT_ID"=:B1 AND "B"."OBJECT_TYPE"='TABLE'))
   3 - filter("OBJECT_ID"=:B1 AND "B"."OBJECT_TYPE"='TABLE')

Note
-----
   - cpu costing is off (consider enabling it)

我们发现了虽然关闭了子查询为表连接,优化器也回到了8I的版本,这里只是表连接的方式改为了filter,然后依然是以外部的表作为驱动表,子查询的表作为被驱动表来做关联的,这个对于之前我们推断in一定先走子查询为驱动表是不合理的:
1 子查询展开为表连接下,此时是两个结果集的关联,优化器为选择少的结果集为驱动表,in的方式不能保证此时优化器一定先选择子查询为驱动表
2 子查询不展开表连接下,此时优化器会选择filter关联方式,优化器选择了主查询的表为驱动表,做filter关联子查询的结果集,in的方式依然不能保证此时优化器一定先走子表

可能有朋友对上面的论点2有异议:优化器这里做了一个查询转换,将in改写为了exists的方式,这个好像是为了满足优化器的驱动准则,就是用小的结果集去做驱动表,这里xiaoyu04比较少,所以这里选择用xiaoyu04去做驱动表是最佳的,而in的写法优化器得先走子表xiaoyu03,然后再走主表xiaoyu04,查询转换in为exists后,优化器可以先走xiaoyu04然后走xiaoyu03了,这个貌似看起来非常合理了,事实真是如下?

这里我们改变一下查询的关系,xiaoyu03是大表在主查询中,xiaoyu04是小表在子查询中,优化器回到11g的版本,而且此时我们还建立xiaoyu03的object_id的一个高效索引,再来看下no_unnest hint作用下的关联顺序:
SQL> create index ind_objid_xiaoyu03 on xiaoyu03(object_id);

Index created.
SQL>  explain plan for select * from xiaoyu03 a where a.object_id in(select /*+no_unnest*/b.object_id from xiaoyu04 b where b.object_type='TABLE');

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2805971548

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |    88 | 14764   (1)| 00:02:58 |
|*  1 |  FILTER            |          |       |       |            |          |
|   2 |   TABLE ACCESS FULL| XIAOYU03 |  9811 |   843K|    43   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| XIAOYU04 |     1 |    10 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "XIAOYU04" "B"
              WHERE "B"."OBJECT_ID"=:B1 AND "B"."OBJECT_TYPE"='TABLE'))
   3 - filter("B"."OBJECT_ID"=:B1 AND "B"."OBJECT_TYPE"='TABLE')

17 rows selected.
还是以主查询的表为驱动表做filter关联。

同样我们再来看看exists是否也是如此,关闭子查询为表连接后,优化器回到11g的版本,exists的sql语句能否将小结果集的子查询作为驱动表,而将大结果集的外部表作为被驱动表了:
SQL> explain plan for select * from xiaoyu03 a where exists(select /*+no_unnest*/1 from xiaoyu04 b where b.object_type='TABLE' and a.object_id=b.object_id);

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2805971548

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |    88 | 14764   (1)| 00:02:58 |
|*  1 |  FILTER            |          |       |       |            |          |
|   2 |   TABLE ACCESS FULL| XIAOYU03 |  9811 |   843K|    43   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| XIAOYU04 |     1 |    10 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "XIAOYU04" "B"
              WHERE "B"."OBJECT_ID"=:B1 AND "B"."OBJECT_TYPE"='TABLE'))
   3 - filter("B"."OBJECT_ID"=:B1 AND "B"."OBJECT_TYPE"='TABLE')

17 rows selected.

看来对于关闭子查询为表连接后,exists确实还是只能先扫描外部的表为驱动表,然后去驱动子查询的表。

这里再次要推翻我们之前的理解:就是优化器这里选择驱动表是考虑了选择小的驱动结果集为驱动表,会通过查询转换in和exists来满足小的结果集去做驱动表。

那么优化器为什么无论in还是exists的方式,在关闭子查询为表连接的特性后,都是选择的外部的表作为驱动表了,其实解释这个现象的根本原因是关联方式filter。

filter究竟如何工作:
表关联方式filter跟nested loop是相同的,只是filter维护了一个hash table用于记录了满足主表和子表关联的关联列和这个关联列相对应的这行数据的部分列。

确实很不好解释这个filter,我们举个简单的例子吧:比如查询是select * from xiaoyu03 a where exists(select /*+no_unnest*/1 from xiaoyu04 b where b.object_type='TABLE' and a.object_id=b.object_id);

oracle这里如果走fiter的关联方式,先从xiaoyu03表取出一条数据,然后a.object_id=b.object_id和b.object_type='TABLE'关联,如果满足,则记录a.object_id的值到hash table,返回满足这个关联条件的a.object_id对应的数据列;然后重复的取出所有xiaoyu03表的数据,如果发现下一个a.object_id已经记录到了hash table中则优化器不再需要去和xiaoyu04表做关联,而是直接再次返回a.object_id对应的这行数据列,这种filter关联方式只能选择主表做驱动表,所以我们看见了在关闭子查询转换为表连接后in和exists对应的filter执行计划都是以外部的表去做驱动表。

所以一般而言filter相比nested loop高效一点,对于重复的满足的关联列节省被驱动表的IO消耗。

相关文章

PHP速学视频免费教程(入门到精通)
PHP速学视频免费教程(入门到精通)

PHP怎么学习?PHP怎么入门?PHP在哪学?PHP怎么学才快?不用担心,这里为大家提供了PHP速学教程(入门到精通),有需要的小伙伴保存下载就能学习啦!

下载

相关标签:

oracle

本站声明:本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn

相关专题

更多
Navicat创建数据库教程
Navicat创建数据库教程

本专题整理Navicat创建数据库相关教程,介绍新建前的准备、基础设置和创建后的检查方法。内容兼顾入门学习及实际使用需求,帮助用户了解不同数据库类型下的操作差异,为后续建表、保存数据和日常管理提供参考。

2026.11.20

20

7

Navicat入门教程大全
Navicat入门教程大全

本专题整理Navicat入门教程,从认识软件界面到开展数据库操作,介绍初次使用需要了解的知识和方法。内容兼顾连接配置、数据管理及日常使用中的疑问,方便用户按学习进度查阅,为后续实践和独立操作打好基础。

2026.11.20

20

17

Navicat数据库连接配置指南
Navicat数据库连接配置指南

从确认数据库信息到建立可用连接,本专题串联Navicat连接数据库的相关知识和操作方法。既有初次配置时需要了解的基础内容,也有连接后的检查思路,方便用户按实际需求查阅教程,逐步熟悉数据库连接的设置流程。

2026.11.20

0

10

PHP8.6数组与数据处理教程
PHP8.6数组与数据处理教程

本专题汇总PHP8.6数组与数据处理教程,涵盖CSV读取和导出、引用遍历删除、array_filter回调模式、配置键名冲突、pathinfo文件名校验、GMP大整数进制转换及UTF-8正则筛选,说明升级兼容变化、输入范围和失败处理方法。

2026.11.20

60

8

PHP8.6升级迁移教程
PHP8.6升级迁移教程

本专题汇总PHP8.6升级迁移与弃用接口改造教程,涵盖反射调用、对象属性赋值、标识符更名、构造函数与__debugInfo调整,以及插件类名检查、常量导出、ArrayIterator排序、SPL和DTO查询参数转换,帮助梳理弃用提示并完成代码兼容改造。

2026.11.20

0

10

PHP8.6安装配置与FPM运维教程
PHP8.6安装配置与FPM运维教程

本专题汇总PHP8.6正式版安装部署与FPM运维教程,涵盖Linux并存安装、独立ini配置、扩展重新编译、Nginx切换与回滚、FPM状态监控和慢日志排查,以及环境变量、请求超时、OPcache刷新和预加载配置。

2026.11.20

40

10

Nginx CVE-2026-123007排查与防护指南
Nginx CVE-2026-123007排查与防护指南

Nginx HTTP/2漏洞该怎么修复?本专题围绕CVE-2026-123007,介绍安全版本升级、模块兼容检查和配置恢复方法,以及日志排查、权限设置等后续加固操作,方便你按实际部署情况查找对应教程。

2026.11.20

40

8

GoFrame框架Web开发与路由中间件
GoFrame框架Web开发与路由中间件

本专题聚焦GoFrame框架的Web开发,拆解路由注册与匹配规则、请求参数获取、参数校验与中间件执行顺序,说明统一响应、JWT鉴权、文件上传与接口文档生成的落地方式,并补充优雅重启与测试实践。

2026.11.17

80

15

GoFrame框架安装与gf命令行工具
GoFrame框架安装与gf命令行工具

本专题围绕GoFrame框架的安装与工具链配置展开,讲清Go版本要求、gf命令行的安装方式、代理镜像与依赖准备,说明项目初始化、热编译、交叉编译打包的做法,并整理安装阶段的常见报错与验证步骤。

2026.11.17

100

15

热门下载

更多
网站特效
/
网站源码
/
网站素材
/
前端模板

精品课程

更多
相关推荐
/
热门推荐
/
最新课程