Oracle 查询转换初探

缺乏、安全感 2022-07-30 13:25 191阅读 0赞

# 概述 #

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

## 子查询展开 ##

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

·         any(= any和in等价)

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

·         exists

·         not exists

single row条件（where后面接=,<,>,<=,>=等条件）  
子查询展开的例子：  
![28218939_14574054594O1R.png][]

最终转换的语句：  
![28218939_14574055032juB.png][]

可以看到子查询中的dept表被拆出来，与外部查询块的emp表做inner join。可以这样转换的前提是dept表的deptno列是唯一键。如果deptno列不是唯一键，将做semi join（即所谓的半连接）：  
![28218939_14574055611Gf8.png][]

如果不做子查询展开，就会走filter类型的执行计划，并且子查询放在最后一步执行，作用是对emp全表扫描之后的结果集进行过滤：  
![28218939_1457405641FhD3.png][]  
看一个子查询结果集作为内联视图与外层查询块做join的例子：  
![28218939_1457405727Gq1z.png][]

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

下面的执行计划中，子查询的结果被作为nest loop的驱动表，为保证结果集正确，需要对子查询做hash unique去重。  
![28218939_1457405796gm5y.png][]

如果满足（不限于）下面的条件，子查询展开可能导致转换不等价，因此将不作展开：

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

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

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

例如下面的例子：  
![28218939_1457405839ZCkl.png][]

## 视图合并 ##

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

如果不做视图合并，执行计划如下：  
![28218939_1457405932AawT.png][]

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

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

·         connect by子句

·         rownum伪列  
做这些限制是为了防止视图合并之后得到错误的结果集。  
不能视图合并的例子：  
![28218939_1457405975SCPp.png][]

### 复杂视图合并 ###

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

![28218939_1457406029giGl.png][]

\_COMPLEX\_VIEW\_MERGING参数控制是否激活复杂视图合并，在9i之后默认为true，同时受OPTIMIZER\_FEATURES\_ENABLE参数控制：  
![28218939_1457406115xWEE.png][]

### 外连接视图合并 ###

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

·         视图为外连接的驱动表

·         视图的定义只包含单表  
下例中视图v1包含两张基表，在做外连接的驱动表时发生视图合并，做被驱动表则没有。  
![28218939_1457406282NLz3.png][]![28218939_1457406291HHIk.png][]

## 谓词推入 ##

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

注意到执行计划中条件EMPLOYEE\_ID<205被推入到视图内部，将两张基表各过滤一次，然后对结果集做union。  
两表关联时，连接条件也可以做推入，先来看不做谓词推入的执行计划：![28218939_1457407540hZ4u.png][]

执行计划中emp13作为驱动表与departments表做nest loop，我们使用hint强制发生谓词推入：  
![28218939_14574076341vZ4.png][]

可以看到执行计划中出现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：优化器将考虑基于成本的星型查询转换，但是转换中不会使用临时表。  
首先看一下星型转换的例子：

![28218939_1457407757UqHs.png][] ![28218939_1457407767Cw1I.png][]

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

![28218939_14574140648Ka8.png][]

下面看一下star\_transformation\_enabled参数设置为true的结果：

![28218939_1457414633oVXs.png][]  
![28218939_1457414680O30O.png][]  
![28218939_14574147264rOO.png][]  
![28218939_1457414777jwYJ.png][]

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

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

[28218939_14574054594O1R.png]: http://blog.itpub.net/attachment/201603/8/28218939_14574054594O1R.png
[28218939_14574055032juB.png]: http://blog.itpub.net/attachment/201603/8/28218939_14574055032juB.png
[28218939_14574055611Gf8.png]: http://blog.itpub.net/attachment/201603/8/28218939_14574055611Gf8.png
[28218939_1457405641FhD3.png]: http://blog.itpub.net/attachment/201603/8/28218939_1457405641FhD3.png
[28218939_1457405727Gq1z.png]: http://blog.itpub.net/attachment/201603/8/28218939_1457405727Gq1z.png
[28218939_1457405796gm5y.png]: http://blog.itpub.net/attachment/201603/8/28218939_1457405796gm5y.png
[28218939_1457405839ZCkl.png]: http://blog.itpub.net/attachment/201603/8/28218939_1457405839ZCkl.png
[28218939_145740588465jv.png]: http://blog.itpub.net/attachment/201603/8/28218939_145740588465jv.png
[28218939_1457405932AawT.png]: http://blog.itpub.net/attachment/201603/8/28218939_1457405932AawT.png
[28218939_1457405975SCPp.png]: http://blog.itpub.net/attachment/201603/8/28218939_1457405975SCPp.png
[28218939_1457406029giGl.png]: http://blog.itpub.net/attachment/201603/8/28218939_1457406029giGl.png
[28218939_1457406115xWEE.png]: http://blog.itpub.net/attachment/201603/8/28218939_1457406115xWEE.png
[28218939_1457406282NLz3.png]: http://blog.itpub.net/attachment/201603/8/28218939_1457406282NLz3.png
[28218939_1457406291HHIk.png]: http://blog.itpub.net/attachment/201603/8/28218939_1457406291HHIk.png
[28218939_1457406694Sb63.png]: http://blog.itpub.net/attachment/201603/8/28218939_1457406694Sb63.png
[28218939_1457407540hZ4u.png]: http://blog.itpub.net/attachment/201603/8/28218939_1457407540hZ4u.png
[28218939_14574076341vZ4.png]: http://blog.itpub.net/attachment/201603/8/28218939_14574076341vZ4.png
[28218939_1457407757UqHs.png]: http://blog.itpub.net/attachment/201603/8/28218939_1457407757UqHs.png
[28218939_1457407767Cw1I.png]: http://blog.itpub.net/attachment/201603/8/28218939_1457407767Cw1I.png
[28218939_14574140648Ka8.png]: http://blog.itpub.net/attachment/201603/8/28218939_14574140648Ka8.png
[28218939_1457414633oVXs.png]: http://blog.itpub.net/attachment/201603/8/28218939_1457414633oVXs.png
[28218939_1457414680O30O.png]: http://blog.itpub.net/attachment/201603/8/28218939_1457414680O30O.png
[28218939_14574147264rOO.png]: http://blog.itpub.net/attachment/201603/8/28218939_14574147264rOO.png
[28218939_1457414777jwYJ.png]: http://blog.itpub.net/attachment/201603/8/28218939_1457414777jwYJ.png