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要具体分析如何利用这些技术。查询转换还有诸如子查询合并，连接因式分解，表扩展，表裁剪，物化视图重写等技术。有机会将再写文章介绍，或者有兴趣的同学自行研究。