oracle常见查询转换笔记

Oracle里的查询转换是指在解析SQL时，对其进行等价改写，目的是为了能更高效的执行目标SQL，即Oracle可能会将目标SQL改写成语义上完全等价但执行效率更高的形式。

 

 

子查询展开

简述

是指优化器不再将目标SQL中的子查询当做一个独立的单元处理，而是将该子查询转换为它自身与外部查询之间等价的表连接。

这种等价要么将子查询拆开，和外部表或视图做连接。要么不拆开但是会把该子查询转换为一个内嵌视图，再和外部表视图做表连接。

适用

存在子查询，子查询前where条件如果是如下这些条件之一，那么这种类型目标SQL满足一定条件后可以做子查询展开：SINGLE-ROW(=,>,<,<=,>=和<>))、exits、 not exits 、in 、not in 、any、 all

注意事项

10g以后针对不拆开子查询但是会将子查询转换为一个内部视图的情况，会比较转换后成本值。只有成本值更小，才会进行子查询展开。

而针对上文提到的第一种情况，不考虑成本值，Oralce始终认为子查询展开效率更高。

如果不做子查询展开，通常该子查询会最后一步执行，走FILTER类型执行计划，即针对外部查询每一条记录，子查询都会单独执行一次。

 

 

视图合并

简述

是指优化器不再将目标SQL中视图当做一个独立单元处理，而是将其拆开，将定义的表与外部查询表合并，不再有视图出现。

适用

存在视图的SQL。

注意事项

Oralce将视图合并分为1. 简单视图（不包含distinctgroup by等聚合函数）合并、2.外连接视图合并、3.复杂视图合并。

针对简单视图，始终会做视图合并。

针对复杂视图，10g以后版本，能否合并取决于成本值变小。

针对外连接视图合并有很常用的限制，即当目标视图在和外部查询表做外连接时，该目标视图可以做外连接视图合并的前提条件是，要么该视图作为外连接的驱动表，或者该视图虽被作为被驱动表但它的视图定义只包含一个表。

执行计划是否存在VIEW，跟是否进行视图合并无关。比如distinct复杂视图即使进行了视图合并也会出现关键字VIEW。

 

 

星型转换

简述

核心是将原星型连接中针对各个维度表的限制条件，通过等价改写的方式以额外的子查询施加到事实表上，然后通过对事实表上各连接列上已存在的位图索引间的位图操作，如位与，位或，来有效减少上待访问的数据量。

注意事项

star_transformation_enabled  
FALSE

The transformation will not be applied.

TRUE

The optimizer willconsider performing a cost-based query transformation on the star query.

TEMP_DISABLE

The optimizer willconsider performing a cost-based query transformation on the star query butwill not use temporary tables in the star transformation.

TRUE和TEMP_DISABLE都表示启用星型转换，区别在于TEMP_DISABLE不会考虑创建临时表而TRUE会。

http://docs.oracle.com/cd/E11882_01/server.112/e40402/initparams253.htm#REFRN10213

 

 

连接谓词推入

简述

是指虽然优化器还是会把该SQL中的视图定义当做一个单独的处理单元执行，但此时优化器会将原本处于该视图外部查询中和该视图之间的连接条件推入到该视图的定义SQL语句内部，目的是能使用该视图基表上的索引，从而走基于索引的嵌套循环连接。

适用

带有视图的SQL

注意事项

做谓词推入时，Oralce会考虑成本，只有经等价改写后成本值变小，才会考虑做谓词推入。

Oracle仅仅支持对如下类型的视图做连接谓词推入。

视图定义中包含 union all/union/distinct/group by的视图。

和外部查询之间连接类型是外连接、反连接、半连接中的一种。

 

 

表扩展

简述

是指当目标SQL中分区表的某个局部分区索引由于某种原因不可用，Oralce能将原目标SQL等价改写成按分区UNION ALL形式。这样其他分区可以正常使用该局部分区索引。

适用

含有分区表目标SQL

注意事项

/*+expand_table/

 

 

表移除

简述

是指优化器会把虽然在目标SQL中存在，但其存在与否对最终执行结果没有影响的表从目标SQL中移除。这样可以少做一次表连接。

适用

多表连接SQL，表与表之间外键关联，外连接等情景。

 

整理自崔华《基于Oralce的SQL优化》