概述

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列不是唯一键,将做semi join(即所谓的半连接):

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

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

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

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

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

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

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

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

例如下面的例子:

视图合并

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

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

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

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

·         connect by子句

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

复杂视图合并

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


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

外连接视图合并

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

·         视图为外连接的驱动表

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

谓词推入

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

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

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

可以看到执行计划中出现PUSHED PREDICATE字样,条件e.department_id=d.department_id被转换成等值条件对employees表做过滤。join的谓词推入往往产生nest loop的执行计划(驱动表的每一行驱动被驱动表,来做谓词的过滤)。如果是大数据集的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要具体分析如何利用这些技术。查询转换还有诸如子查询合并,连接因式分解,表扩展,表裁剪,物化视图重写等技术。有机会将再写文章介绍,或者有兴趣的同学自行研究。