专栏首页沃趣科技oracle 查询转换初探

oracle 查询转换初探

Oracle‍‍查询转换初探

作者‍:邱大龙‍‍

概述

Oracle查询转换器的作用是把原始sql重写为语义相同的语句,目的是为了获得更高效的sql。 查询转换主要有四种技术:子查询展开,视图合并,谓词推入,星型转换。 了解查询转换是掌握SQL优化的基础,本文将对这四种技术做一些简单的介绍。

子查询展开

子查询展开是指优化器不再把子查询作为独立的单元处理,而是转换成等价的join方式。转换有两种方式:一是将子查询的结果集作为视图,与外层表或视图做join;二是将子查询中的表或视图拆出来,与外层表或视图做join。子查询前包含以下条件可以被展开:

· any(= any和in等价)

· all(<> all和not in等价)

· exists

· not exists

· single row条件(where后面接=,<,>,<=,>=等条件) 子查询展开的例子:

最终转换的语句:

可以看到子查询中的dept表被拆出来,与外部查询块的emp表做inner join。可以这样转换的前提是dept表的deptno列是唯一键。如果deptno列不是唯一键,将做semijoin(即所谓的半连接):

如果不做子查询展开,就会走filter类型的执行计划,并且子查询放在最后一步执行,作用是对emp全表扫描之后的结果集进行过滤:

看一个子查询结果集作为内联视图与外层查询块做join的例子:

执行计划仍然走了hashjoin semi,要使得转换是等价的,必须先完成子查询中departments和locations的join,结果集作为内联视图VM_NSO_1,与外层查询块的结果集做join。

下面的执行计划中,子查询的结果被作为nestloop的驱动表,为保证结果集正确,需要对子查询做hash unique去重。

如果满足(不限于)下面的条件,子查询展开可能导致转换不等价,因此将不作展开:

· where后面的连接符为=all或者<>any

· exists后面的子查询中带有rownum

· exists后面的子查询中带有having子句,cube子句或者rollup子句

例如下面的例子:

视图合并

视图合并是指对包含视图的查询做出转换,使查询只包含基表。视图合并提供了更多的访问路径和join的可能性。也就是说,不做视图合并的执行计划包含在做了视图合并的执行计划中。下面的例子可以帮助理解这句话:

如果不做视图合并,执行计划如下:

可以看到不对emp100做视图合并,执行计划中出现view字样,name列对应的就是视图名emp100。 第二个执行计划除了多了view的一行,访问路径和成本是和第一个相同的。 如果视图定义中包含下列内容,将不能做视图合并:

· 集合操作符(UNION,UNIONALL,INTERSECT,MINUS)

· connectby子句

· rownum伪列 做这些限制是为了防止视图合并之后得到错误的结果集。 不能视图合并的例子:

复杂视图合并

复杂视图合并技术允许对包含gourp by或者distinct的视图做展开。

_COMPLEX_VIEW_MERGING参数控制是否激活复杂视图合并,在9i之后默认为true,同时受OPTIMIZER_FEATURES_ENABLE参数控制:

外连接视图合并

使用了外连接的sql中,视图合并需要满足下列条件之一:

· 视图为外连接的驱动表

· 视图的定义只包含单表 下例中视图v1包含两张基表,在做外连接的驱动表时发生视图合并,做被驱动表则没有。

谓词推入

优化器在处理不能合并的视图时,可以选择将外部查询的谓词推入该视图的查询块,或者将视图中的谓词拉出到主查询。这样更早的处理视图的结果集,有可能会减小后续步骤操作所需的成本。 谓词推入到视图内部的例子:

注意到执行计划中条件EMPLOYEE_ID<205被推入到视图内部,将两张基表各过滤一次,然后对结果集做union。 两表关联时,连接条件也可以做推入,先来看不做谓词推入的执行计划:

执行计划中emp13作为驱动表与departments表做nest loop,我们使用hint强制发生谓词推入:

可以看到执行计划中出现PUSHEDPREDICATE字样,条件e.department_id=d.department_id被转换成等值条件对employees表做过滤。join的谓词推入往往产生nestloop的执行计划(驱动表的每一行驱动被驱动表,来做谓词的过滤)。如果是大数据集的sql,可以使用hint no_push_pred或者设置参数_push_join_predicate为false禁止谓词推入。

星型转换

星型转换为提高星型查询的效率发生,在原有条件基础上会产生新的子查询对事实表做过滤,然后通过对事实表相应连接列的位图索引做位图操作,达到过滤事实表结果集的目的。 是否开启星型转换受参数star_transformation_enabled控制,可以设置为: true:优化器将考虑基于成本的星型查询转换; false:禁止星型转换; temp_disable:优化器将考虑基于成本的星型查询转换,但是转换中不会使用临时表。 首先看一下星型转换的例子:

注意到执行计划首先对各个维度表过滤出结果集,然后访问sales表连接列上的索引,做bitmap and操作之后,回表访问数据。事实上整个过程类似于将查询转换为如下等价sql:

下面看一下star_transformation_enabled参数设置为true的结果:

注意到执行计划中先对customers根据过滤条件cu.country_id=52789得到临时表SYS_TEMP_0FD9D6601_11F1D1,后续步骤中每次需要访问customers表时则由临时表来替换,这也是为了总共减少所访问的数据量所考虑。 星型转换同样有一些限制条件,本文暂不讨论。

以上是对四类查询转换概念性的描述,对于具体的应用场景中的SQL要具体分析如何利用这些技术。查询转换还有诸如子查询合并,连接因式分解,表扩展,表裁剪,物化视图重写等技术。有机会将再写文章介绍,或者有兴趣的同学自行研究。

本文分享自微信公众号 - 沃趣科技(woqutech)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2016-03-10

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 按 host 分组统计视图 | 全方位认识 sys 系统库

    在上一篇《配置表 | 全方位认识 sys 系统库》中,我们介绍了sys 系统库的配置表,但实际上我们大部分人大多数时候并不需要去修改配置表,直接使用sys 系统...

    沃趣科技
  • 按 user 分组统计视图|全方位认识 sys 系统库

    在上一篇《按 host 分组统计视图 | 全方位认识 sys 系统库》中,我们介绍了sys 系统库中按 host 分组统计的视图,类似地,本期的内容将为大家介绍...

    沃趣科技
  • 内存分配统计视图 | 全方位认识 sys 系统库

    在上一篇《按 file 分组统计视图 | 全方位认识 sys 系统库》中,我们介绍了sys 系统库中按 file 分组统计的视图,本期的内容将为大家介绍内存事件...

    沃趣科技
  • 大数据入门基础系列之详谈Hive的视图

    在前面的博文里,我已经介绍了 问:哪个版本开始Hive开始支持视图了? 答:Hive0.6开始 可以先,从MySQL里的视图概念理解入手 视图是由从数据库的基本...

    企鹅号小编
  • 翻译_iOS视图编程指南(View Programming Guide for iOS)之视图和窗口体系

    前些日子,我发布一个苹果官方文档的翻译,之后就有不少同学朋友问我:翻译苹果官方文档能做什么,开发过程用到的时候很少,浪费时间,还又没什么用。今天,刚好有时间,就...

    Jacklin999
  • SwiftUI:使用 @EnvironmentObject 从环境中读取自定义值

    SwiftUI的环境使我们可以使用来自外部的值,这对于读取Core Data上下文或视图的展示模式等很有用。但是我们也可以将自定义对象发送到环境中,并在以后将它...

    韦弦zhy
  • MySQL引擎和视图的点

    存储引擎Storage Engine:MySQL中的数据、索引以及其他对象是如何存储的,是一套文件系统的实现。

    孙玄@奈学教育
  • Mysql进阶三板斧(一)带你彻底搞懂View视图的原理及应用

    既然视图的定义是基于基本表的,哪为什么还要定义视图呢?这是因为合理地使用视图能够带来许多好处:

    陈哈哈
  • MySQL 视图

    看到这里,或许你已经对MySQL 的基本操作了如指掌,这篇文章讲解MySQL高级功能中 视图的概念及其用法。

    技能锦囊
  • 翻译_iOS视图编程指南(View Programming Guide for iOS)之介绍

    在iOS中,你可以使用窗口和视图将你应用的内容呈现在屏幕上。窗口本身是不具备呈现可视化内容的功能的,但它可以用作装有应用视图的容器。视图可以规定在窗口的某一部分...

    Jacklin999

扫码关注云+社区

领取腾讯云代金券