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)

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

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Hadoop数据仓库

HAWQ与Hive查询性能对比测试

一、实验目的         本实验通过模拟一个典型的应用场景和实际数据量,测试并对比HAWQ内部表、外部表与Hive的查询性能。 二、硬件环境 1. 四台VM...

2756
来自专栏枕边书

空间索引 - 各数据库空间索引使用报告

空间索引 索引我们都用过,它是一种特殊的存储结构,就像图书馆里书的分类存放策略或是现代化图书馆里的图书查询系统,能帮助我们快速找到自己需要的书。 数据库中,索引...

9788
来自专栏java达人

认识九大经典sql模式

小结果集,源表较少,查询条件直接针对源表 对于典型的OLTP应用,多为返回小结果集的查询。如果过滤条件直接针对源表,我们必须保证这些过滤条件高效,对于重要的字...

2338
来自专栏互联网开发者交流社区

SQL基础用法(实例二)

1407
来自专栏数据和云

让SQL成为一种生活方式:认识分页查询

? 1分页查询的书写方式 分页的目的就是控制输出结果集大小,将结果尽快的返回。 Oracle的分页查询语句有两种基本格式。第一种格式如下: ? 上面给出的这个...

3156
来自专栏用户画像

网上书店管理系统数据库 sql sever

1.数据库各数据对象的设计与实现:表、约束、完整性体现、查询、视图,要求用合理的数据体现。

1423
来自专栏java一日一条

优化SQL查询:如何写出高性能SQL语句

执行计划是数据库根据SQL语句和相关表的统计信息作出的一个查询方案,这个方案是由查询优化器自动分析产生的,比如一条SQL语句如果用来从一个 10万条记录的表中查...

1361
来自专栏JetpropelledSnake

SQL学习之简单增删改查

2516
来自专栏java达人

Java开发者编写SQL语句时常见的10种错误

Java开发者对于面向对象编程思维与命令行编程思维的协调程度,取决于他们如下几种能力的水平: 1. 技巧(任何人都可以编写命令行形式的代码) 2. 教条(有的...

2575
来自专栏GreenLeaves

Oracle 维护数据的完整性 一 索引

简介:索引是用于加速数据存取的数据对象,合理的使用索引可以大大降低i/o 次数,从而提高数据的访问性能. 当我们从一张表中检索我们需要的数据是,oralce往往...

1916

扫码关注云+社区

领取腾讯云代金券