ORACLE SQL 高级编程--查询转换

查询转换      

在进展到执行计划的开发过程之前，会有一步被称为查询转换的步骤。该步骤发生在一个查询进行完语法和权限检查之后，优化器为了决定最终的执行计划而为不同的计划计算成本预估之前，换句话说，转换和优选是两种不同的任务。

     在你的查询通过了语法的权限检查之后，查询就进入了转换为一系列查询块的转换阶段。查询是通过select关键来定义的。例如，select * from employees where department_id=60 这个查询只有一个查询块，而select * from employees where department_id in (select department_id from departments)有两个查询块。各个查询块要么嵌在另一个查询块中，要么以某种方式与另一个查询块相联结。查询书写的方式决定了查询块之间的关系。查询转换的主要目的就是确定如果改变查询的写法会不会提供更好的查询计划。

     请确定你理解了最后一句话。查询转换能够并且可能重写你的查询。这也许是你从未认识到的。你所写的并不一定就是最终确定执行计划的语句。很多时候这是件好事。查询转换器知道优化器是如何处理特定语法的，并且将会做任何它能做的事情来以某种方式转化你的SQL语句以帮助优化器选出最好、最高效的执行计划。但是，你所写的东西能够被改变可能就意味着你所期望的行为可能不是像你想要的那样，尤其是你想要的语句的一定部分执行的顺序。因此，你确定需要理解查询转换是如何工作的，以便能够写出可以正确得到你想要的行为的SQL语句。

     查询转换器可能会改你最初所写查询的结构，只要这样的改变不会影响结果集。任何可能会导致结果集与原有查询语法结果集不同的改变都不在考虑之列。最常进行的改变就是将独立的查询块转换为直接联结。例如，这个语句

      select * from employees where department_id in (select department_id from departments)

可能将会被转换为下面语句

     select e.* from employees e,departments d where e.department_id=d.department_id

 结果集没有改变，但是从优化器的角度来看转换后的版本执行计划的选择将会更好。

      在你知道要寻找什么信息之后，你常常能够通过查看执行计划来了解是否发了查询转换。你也可以通过使用NO_QUERY_TRANSFORMATION提示，然后比较该查询和没有使用这个提示的查询的执行计划。如果这两个计划不一样，区别可能就来自于查询转换。当使用此提示时候，除了谓语前推（predicate pushing,稍后将讲到）以外的查询转换都将被禁止。

      以下几种基本的转换能够应用到特定的查询中：

      。视图合并；

      。子查询解嵌套；

      。谓语前推；

      。使用物化视图进行查询重写。

一、视图合并

     正如其名字所暗示的，视图合并是一种能将内嵌或储存式视图展开为能够独立分析或者与查询剩余部分合并成总体执行计划的独立查询块的转换。改写后的语句基本上不包含视图。一个如select * from my_view的语句将会被改写为好像直接输入了视图源一样。视图合并常常发生在当外部查询块的谓语包括下列项的时候：

    。能够在另一个查询块的索引中使用的列。

    。能够在另一个查询块的分区截断中所使用的列。

    。在一联结视图中能够限制返回行数的条件。

大多数人相信视图总是被作为独立的查询块对待并总是总有自己的子查询计划，并且在与其的查块联结之前执行。但由于查询转换的存在，这是不正确的。实际情况是有时视图会被独立分析并有自己的子查询计划，但通常情况下，将视图与查询的其他部分合并会对性能有很大的好处。例如，下面的查询根据视图是否进行了合并所使用的资源也会有所不同。

       select * from orders o,(select salas_rep_id from orderss) o_view where o.sales_rep_id=o_view.sales_rep_id(+) and o.order_total>100000;

代码清单列出了当进行视图合并以及不进这行视图合并时该查询的执行计划上，注意每个步骤中计划操作的选择以及A-Rows的计数(计划中这步所获取的实际行数）。

 

 

     注：在第2个进行视图合并的执行计划中视图是单独来进行处理的。该计划还通过在第3行中使用VIEW关键这来表明视图是保持“原样”的，通过单独处理视图，在与外部的orders表联结之前就要对orders表进行全表扫描。然而，在使用视图合并的版本中，计划运算合并为一个计划而不是让内嵌视图保持独立。这就使得所选的对于索引的访问操作效率更高，并且需要处理更少的行(26行 vs 104行)。这个例子使用的还是一个很小的表，因此可以想象如果在查中包含量很大的表的话将会做多少工作。对视图进行合并的转换使得总休执行计划变得更佳。

       这里还存在一种误解，在一个查询中，内嵌视图或标准视图将会与查询的其部分分开并被首先考虑。这可能源于我们所接受的关于数学运算执行顺序的教育。让我们来看下面的例子。

    6+4/2=8

     (6+4)/2=5

    第二个例子中的圆括号使得加法运算运行，而在第一个例子中根据运算顺序除法将会先做。我们被告知当使用圆括号的时候其中的运算将会先发生。但是SQL语言并不遵循与数学表达式同样的规则。使用圆括呈某个查询块与其他部分分开并不能保证该查询块将会单独或首先执行。如果在所写的语句中包含了一个内嵌视图，假如想让该视图被单独考虑，你可能需要在查询块中加入NO_MEGER提示以防止它被重写。事实上，我能够在如上的SQL中生成不合并视图的执行计划也正是因为使用了NO_MERGE提示。通过这个提示，我就能够告诉查询转换器我想要让o_view查询块被独立于其他外部查询块来单独考虑。使用提示的查询实际上看起来像这样：

    select * from orders o,

    (select /*+ NO_MERGE */ sales_rep_id from orders ) o_view

   where o.sales_rep_id=o_view.sales_rep_id(+)

   and o.order_total >100000;

   还有其他一些情况(如果出现的话)也会阻止视图合并的发生。如果一个查询块包含解析函数或聚合函数、集合运算（例如UNION、INTERSECT、MINUS），ORDER BY子句或者使用了ROWNUM，视图合并将会被禁止或限制。即使用出现了上面的某些情形，你也可能通过使用MERGE提示来强制执行视图合并。如果你通过该提示强制进行了视图合并，你必须确认查询的结果集在视图合并后仍是正确的。如果没有发生视图合并，那有可能因为视图合并将会导致查询结果不同。一旦使用了这个提示，也就表明你认视图合并的语句并不会影响结果。如下展示的是一个含有聚合函数的不进行视图合并的语句，以及MERGE提地的使用是如何强制视图合并发的例子。

 

        视图合并行为是通过一个隐藏参数complex_view_merging来控制的，在Oralce9及后续版本中该参数的默认值为TRUE。从Oralce 10版本开始，转换后的查询将会由优化器进行复查，视图合并以及不合并的查询计划所需要成本都会被评估。然后优化器就会选择成本最低的执行计划。

 

      二、 子查询解嵌套

    子查询解嵌套与视图合并的相似之处在于子查询也是通过一个单独的查询块来表示的。可合并的视图与可以解嵌套的子查询之间的主要区别在于它们的位置是不同的：子查询位于where句，由转换器进行解嵌套的审查。最典型的转换就是将子查询转变为表联结。如果一个子查询没有解嵌套，将会为它生成一个独立的子计划并作为总的执行计划的一部分按照优化执行速度的次序依次执行。

       当子查询不相关的时候，转换查询是非常直接的，如下

 

 

        本例中的子查询就是简单的地能通过转化为表联结来合并到主查询中。该查询的执行计划好像是按照如下语句来得出的：

     select e.* from employees e,departments d where e.department_id=d.department_id

        通过使用NO_UNNEST提示，我可以强制该查询按照所书写的的方式进行优选，也就意味着将会为子查询单独生成一个子执行计划，如下

 

     这两种执行计划的主要区别就是不进行查询转换将会选用FILTER运算而不是NESTED_LOOPS连接。而FILTER运算是以较低效率进行两个表的区配及联结的典型代表。如果你查看第一步的谓语信息，你可以看到子查询是保持原封不动的。这个“原版”查询在执行的时候对于employees表的每一行数据，子查询必须使用employees表中的department_id这一列来与子查询所返回的department_id来进行联结。由于empolyees表中有107行，每一行都将执行一次子查询。国为Oracle使用了一种很好的被称为子查询缓存的优化功能，这两种执行计划孰优孰劣还不好说，但你很可能会看到为每一行执行一次查询的效率要比表不错结的效率低。后续将讲到这些运算，并且评论为什么NESTED LOOPS联结比FILTERF运算的效率要高。

        当包含联结子查询的时候，子查询解嵌套转换会变得更复杂一些，在本例中，联结子查询一般是转换为一个非嵌套视图，然后与主查中的表相联结。如下为一个联结子查询解嵌套的例子。

  

     注意在这个例子中子查询是如何转换为一个内嵌视图，然后与其外的查询合并且相联结的。相关列变成了联结条件而子查询的剩余部分用来生成内嵌视图。经过重写后的该查询的版本将会像下面这样：

         select outer.employee_id,outer.last_name,outer.salary,outer.department_id from employees outer,

       ( selct department_id,avg(salary) avg_sal from employees group by department_id) inner where outer.department_id=inner.department_id

       子查询解嵌套的行为由隐藏参数_unnest_subquery控制的，在Oracle 9及后纽版本中其默认值为TRUE.该参数被特指为控相关子查询解嵌套行为的参数。与视图合并类似，从Oracle 10版本开始，转换后的查询将会由优化器进行复核，然后根据成本的评估来确定解嵌套的版本是不是成本更低。

 

    三、谓语前推

       谓语前推用来将谓语从一个内含查询块中应用到不可合并的查询中。目标就是允许索引的使用或者让其他对于数据集的筛选在查询中能够更早地进行。一般来说，将不需要的数据行尽可能早的过滤是个好主意。一直要这样想：早点儿进行筛选。

         如下展示了进行谓语前推以及不进行谓语前推之间的区别。

   

       

 

 

            注意第一个执行计划中的第6步。where department_id=60这个谓语被推进到视图中，使得可以仅计算一个部门的平均薪水。当这个谓如第二人执行计划所示不被推进时，需要计算每个部门的平均薪水，然后当外部的查询块与内部查询块相联结的时候，再将所有department_id不为60的数据行剔除掉。你可以通过行数估算也可以通过第二个计划的执行成本看出，优化器认识到该计划不得不等待应用这个谓语而需要做更多的工作，因此是更昂贵并且更费时的运算。

             需要指出的是在上例中我用了一个窍门来阻止谓语前推的发生。在第二个查询中所用的rownum伪列(我增加了where rownum>1的谓语)扮演了禁止谓语前推的角色。事实上，rownum不仅会禁止谓语前推，而且也会禁止视图合并。使用rownum就如同在查询中加入NO_MERGE和NO_PUS_PRED提示。在本例中，它使得我可以指出当不发生谓语前推时的坏影响。但同时我想要确定你认识到了使用rownum在决定执行计划的时候也会影响优化器进行选择。在使用rownum的时候你一定要小心-----它将会使所在的查询块既不能进行视图合并也不能前推谓语。

            除非通过使用rownum或者NO_PUSH_PRED提示，谓语前推不需要你做任何特别的动作就会发生。是你所需要的！尽管在有些极端情况谓语前推会不具优势，但这种情况少之又少。因此，一定要检查执行计划以确保谓语推前如你所期望的那样发生了。

    四、 使用物化视图进行查询重写

      查询重是一种发生在当一个查询或查询的一部分已经被保存为一个物化视图，转换器重写该查询以使用预先计算好的物化视图数据而不需要执行当前查询的转换。物化视图与普通视图的区别在于已经被执行并将结果集存入了一张表中。这样做的好处就是预先计算了查询的结果并且在特定查询执行的时以直接调取该结果。也就是说所有的确定执行计划、执行查询以及收集所有数据的工作都是已经做完了。因此，当同样查询再一次执行的时候，就不需要再做一遍了。

未完待续。。。