oracle查询优化 之查询转换

查询优化器使用大量的查询转换来产生新的语义相等的SQL语句，根据用于决定是否应用他们的方法，可以分为两种途径

基于启发式的查询转换：是在满足特定条件时应用的，在大多数情况下他们预计都会引出更好地执行计划

基于成本的查询转换：根据成本估算器计算的成本而应用的，他们会引出与原始语句相比成本更低的执行计划

下列介绍了几种查询转换的用例，便于了解在逻辑优化阶段引擎内部都发生了什么

1.计数转换

计数转换的目的是将count(列)表达式转化为count(*)。因为相比于count(列)，count(*)在索引使用方面有更多的选择 空间。

计数转换基于启发式的查询转换，可以在count函数引用的列中关联NOT NULL约束时进行应用。注意，计数转换也会将count(1)这样的表达式转换为count(*).

select count(n1) from t

如果有一个NOT NULL约束定义在n1上，计数转换就会将count(n1)装换为count(*)并生成下列的查询语句

select count(*) from t

2.公共子表达式消除

公共子表达式消除的目的是移除重复的谓词从而避免多次处理同一个操作，这是一种基于启发式的查询转换

SELECT * FROM t WHERE (n1 = 1 AND n2 = 2) OR (n1 = 1)

公共子表达式消除冗余的谓词，并产生以下查询语句

SELECT * FROM t WHERE n1 = 1

3.“或”扩张

“或”扩张的目的是将查询的where条件中包含分割谓词的语句转化为使用一个或多个union all集合运算符的复合查询寻，通常情况下，每个被分割的谓词被转化为一个组件查询，这里应用的是一个基于成本的查询转换，大多数的时候是为了启用额外的索引访问路径。实际上，分割的谓词和索引在一起搭配时并不总是进展顺利，还要注意仅从11.2.0.2版本起这种查询转换才开始支持函数式索引。

注意：即使“或”扩张是基于成本的查询转换，查询优化器也会在尝试使用它之前检查一些启发式查询转换。如果查询转换不被允许，可能会错失一个拥有更低成本的执行计划

下列这个例子是在n1 和n2列上都有索引，因为where条件包含两个分割的谓词，查询优化器会评估一次基于表扫描的成本是否高于两次单独的基于索引扫描的成本。

SELECT * FROM t WHERE n1 = 1 OR n2 =2

如果两次扫描的成本更低，“或”扩张就会差生以下的查询。注意添加lnnvl(n1 = 1)这个谓词是为了避免多重记录

SELECT * FROM t WHERE n1 = 1 
UNION ALL
SELECT * FROM t WHERE  n2 =2 AND lnnvl(n1 = 1)

一些分割谓词永远不会被显示的用“或”扩张，下面的查询就展示了这样一个例子，所有的谓词都引用了n1列，是的where条件的内容能够像IN条件那样处理

SELECT * FROM t WHERE  n1 =1 OR n1 = 2  OR n1 = 3

4.视图合并

视图合并的目的是通过合并语句中一部分视图和内联视图，以减少由他们产生的查询块数量。引入这个查询转换的原因是，如果没有它，查询优化器就会分别处理每一个查询块，当分别处理每个查询块时，查询优化器无法保证每次都为SQL语句产生最优的执行计划，此外由视图合并产生的查询块可能会进一步引导启用其他的查询转换

查询块：简单地说，最顶级的SQL语句以及一个SQL语句中拥有自己的SELECT字句的每个扩展部分都是查询块。简单的SQL语句只有一个查询块，而一旦使用了视图或者像子查询、内联视图以及集合运算符这样的结构，多重的查询块就会出现。

视图合并有两个子范畴

    简单视图合并：用于合并简单地选择-投影-连接查询块。因为它所处理情况的简单性，简单视图合并是一种基于启发式的查询转换。它无法应用于包含类似聚合、聚合运算符、层次查询或者select列表中含有子查询这样的视图或内联视图。

    复杂视图合并：用于合并包含聚合的查询块，这是一种基于成本的查询转换，无法应用于有层次查询出现或者包含ROLLUP、PIVOT字句的视图或内联视图。

因为应用复杂的视图合并并不一定能够带来好处，所以它是基于成本的查询转换。实际上，使用它时，物化视图或者内联视图中出现的聚合就被推后了，因此可能导致SQL在一个很大的结果集上执行。

视图合并可能带来安全问题，为了预防这些问题，就提出了安全视图合并的概念，并有初始化参数optimizer_secure_view_merging控制其是否可用。

     1.简单视图合并

在下列的例子中，查询由三个查询块构成：顶层查询和两个内联视图。注意，这两个内联视图是简单的选择-投影-连接查询块

SELECT *
  FROM (SELECT T1.* FORM T1, T2 WHERE T1.ID = T2.ID) T12,
       (SELECT * FROM T3 WHERE ID > 2) T3
 WHERE T12.ID = T3.ID

因为内联视图可以进行合并，简单视图合并差生了一下查询

SELECT T1.*, T3.* FORM T1, T2, T3
 WHERE T1.ID = T2.ID
   AND T1.ID = T3.ID
   AND T3.ID > 2

当涉及外链接时简单视图合并就不一定每次都能执行了，如果在之前的查询中把顶层谓词改成T12.ID = T3.ID(+)，视图合并扔可以执行，但是如果将谓词修改成T12.ID(+) = T3.ID就没法执行视图合并了。

     2.复杂视图合并

下列的例子展示了一个带有GROUP BY自己的内联视图，这样的查询按以下方式执行：访问内联视图中引用的表，评估GROUP BY 字句和sum函数，最后将内联视图的结果集与顶层查询引用的表进行连接：

SELECT T1.ID, T1.N, T1.PAD, T2.SUM_N
  FROM T1, (SELECT N, SUM(N) AS SUM_N FROM T2 GROUP BY N) T2
 WHERE T1.N = T2.N
将GROUP BY 字句的评估推迟知道连接完毕之后有利时，复杂视图合并产生以下查询：

SELECT T1.ID, T1.N, T1.PAD, T2.SUM_N
  FROM T1, T2
 WHERE T1.N = T2.N
 GROUP BY T1.ID, T1.N, T1.PAD, T2.N
5.选择列表裁剪

选择列表裁剪的目的是去掉来自子查询、内联视图以及普通视图的select字句的表达式，这种类型的查询转换不会考虑顶层查询到的select字句，当一个列或者表达式没有在引用或者定义他的select子句外的地方被引用，就会被认为是没有必要的。这是一种基于启发式的查询转换

如下列的例子中，子查询的两个列N2、N3是没有被外层主查询引用的

SELECT N1 FROM (SELECT N1, N2, N3 FROM T)

因为N2、N3这两个列没有必要，所以列表裁剪会移除他们并生成以下查询

SELECT N1 FROM (SELECT N1 FROM T)

使用视图合并，可以进一步简化查询，生成以下查询语句

SELECT N1 FROM T

6.谓词下推

谓词下推的目的是将谓词下推到无法合并的视图或内联视图的内部，能够进行下推的谓词必须包含在拥有不可合并的视图或内联视图的查询块内部。应用这种类型的查询转换有以下三个主要原因

      1.为了启用额外的访问路径（典型的是索引扫描）

      2.为了启用额外的连接方法以及连接顺序

      3.为了确保能够尽可能快得应用谓词，从而避免不必要的处理操作

谓词下推有两个子范畴：过滤谓词下推和连接谓词下推，两种变换的不同是由它们操作的谓词的类型决定的

    1.过滤谓词下推

过滤谓词下推的目的是将限制条件下推到无法合并的视图或内联视图的内部，这是一种基于启发式的查询转换。注意这种查询转换不下推连接条件。下面的例子union集合运算符用来防止内联视图与顶层查询合并

SELECT *
  FROM (SELECT *
          FROM T1
        UNION
        SELECT * FROM T2)
 WHERE ID = 1

过滤谓词下推将过滤条件（ID = 1）下推到内联视图内部并产生下面的查询语句，现在这两张表不仅可以通过索引来访问，同时也保证union集合运算需要的排序操作处理更少的数据记录

SELECT *
  FROM (SELECT *
          FROM T1
         WHERE ID = 1
        UNION
        SELECT * FROM T2 WHERE ID = 1)

此外简单视图合并还消除了顶层查询块

    2.连接谓词下推

连接谓词下推的目的是将链接谓词下推到无法合并的视图或内联视图的内部，是一种基于成本的查询转换

下面的例子中，union集合运算符用来防止内联视图与顶层查询合并

SELECT *  
  FROM T1,
       (SELECT *          
          FROM T2        
        UNION        
        SELECT * FROM T3) T23  
 WHERE T1.ID = T23.ID

链接谓词下推将连接条件（T1.ID = T23.ID）下推到内联视图的内部并产生以下查询

SELECT *  
  FROM T1,
       (SELECT *          
          FROM T2  
         WHERE T1.ID = T2.ID     
        UNION        
        SELECT * FROM T3 WHERE T1.ID = T3.ID) T23  

尽管这个SQL语句并不是有效的（T1.ID在内联视图中是不可见的），但是SQL引擎可以处理与它类似的一些情况。为了支持这样的查询，从12.1版本开始可以使用侧向内联视图。例如下面的查询在12.1版本中是合法的

SELECT *  
  FROM T1,
       lateral(SELECT *          
          FROM T2  
         WHERE T1.ID = T2.ID     
        UNION        
        SELECT * FROM T3 WHERE T1.ID = T3.ID) T23