《Pro Oracle SQL》CHAPTER2--2.7 View Merging

View Merging  视图合并  (Page 62)
    As the name implies, view merging is a transformation that expands views, either in-line views or stored views, into separate query blocks that can either be analyzed separately or that can be merged with the rest of the query to form a single overall execution plan.  Basically, the statement is rewritten without the view.  A statement like select * from my_view would be rewritten as if you had simply typed in the view source.  View merging usually occurs when the outer query block’s predicate contains:
    正如名字所暗示的，视图合并是一种变换：扩展视图，要么是内联视图要么是存储的视图，成独立的查询块能够被分别分析或者能同查询的其余部分合并而形成一整体执行计划。 基本上，语句被重写成没有视图了，如同select * from my_view这样的语句将被重写的像你直接在视图源输入的一样。视图合并通常发生在外部查询块的谓词包含：

•  a column that can be used in an index within another query block.
•  a column that can be used for partition pruning within another query block.
•  a condition that limitis the rows returned from one of the tables in a joined view.
•  有一列能被其他的查询块中的索引所使用。
•  有一列能被其他的查询块中的分区裁剪所使用。
•  有一条件限制在连接的视图中某张表的返回行数。

    Most people believe that a view will always be treated as a separate query block and will always have
its own subplan and be executed prior to joining to other query blocks.  That is not true due to the
actions of the query transformer.  The truth is that sometimes views will be analyzed separately and have
their own subplan, but more often than not, merging views with the rest of the query provides a greater
performance benefit.  For example, the following query might use resources quite differently depending
on whether or not the view is merged:
     大多数人相信视图将总是被作为分散的查询块对待，且总是有它自己的子计划，也在先于连接到其他查询块之前执行。这是不对的，因为查询变换的作用。事实是有时视图将被独立的分析且有他自己的子计划，但是往往，把视图同查询的其它部分合并产生了更好的性能优势。例如，如下查询所用的资源将有非常大的区别，取决于视图是否合并：
select *
from   orders o,
      (select sales_rep_id
             from orders
      ) o_view
where  o.sales_rep_id = o_view.sales_rep_id(+)
and    o.order_total > 100000;
 
    Listing 2-4 shows the execution plans for this query when view merging occurs and when it doesn’t. 
Notice the plan operations chosen and the A-Rows count (actual rows retrieved in that step of the plan)
in each step.
    列表2-4展示这个查询当发生视图合并和没有时的执行计划。注意每一步中计划所选的操作(步骤)和A-Rows计数值(计划中的那一步检索的实际行数)
Listing 2-4. View Merging Plan Comparison
-- View merging occurs
 
--------------------------------------------------------------------------
| Id  | Operation                                | Name                          | Starts | E-Rows   | A-Rows |
--------------------------------------------------------------------------
|   1 |  NESTED LOOPS OUTER          |                                   |      1 |    413     |     31   |
|*  2 |   TABLE ACCESS FULL           | ORDERS                        |      1 |     70      |      7   |
|*  3 |   INDEX RANGE SCAN            | ORD_SALES_REP_IX        |      7 |      6       |     26   |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("O"."ORDER_TOTAL">100000)
   3 - access("O"."SALES_REP_ID"="SALES_REP_ID")
       filter("SALES_REP_ID" IS NOT NULL)

-- View merging does not occur
 
-----------------------------------------------------------------
| Id  | Operation                          | Name       | Starts | E-Rows | A-Rows |
-----------------------------------------------------------------
|*  1 |  HASH JOIN OUTER          |               |      1   |    413   |     31 |
|*  2 |    TABLE ACCESS FULL     | ORDERS    |      1   |     70   |      7 |
|   3  |    VIEW                             |               |      1   |    105   |    104 |
|   4  |      TABLE ACCESS FULL   | ORDERS    |      1   |    105   |    104 |
-----------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("O"."SALES_REP_ID"="O_VIEW"."SALES_REP_ID")
   2 - filter("O"."ORDER_TOTAL">100000)

    Did you notice how in the second, non-merged plan, the view is handled separately?  The plan even
indicates the view was kept “as is” by showing the VIEW keyword in line 3 of the plan.  By treating the
view separately, a full scan of the orders table occurs before it is joined with the outer orders table.  But,
in the merged version, the plan operations are merged into a single plan instead of keeping the in-line
view separate.  This results in a more efficient index access operation being chosen and requires fewer
rows to be processed (26 vs 104).  This example uses small tables, so imagine how much work would
occur if you had really large tables involved in the query.  The transformation to merge the view makes
the plan perform more optimally overall.
    你注意到在第二个，没有合并的计划中，视图是单独处理的么？计划中甚至指出视图被保持，“当做是”，在计划的第三行显示VIEW关键字。由于独立的处理视图，在它连接到外面的order表之前要发生一次对(内部)order表的全表扫描。但是，对于合并的版本，计划操作被合并成一个计划而不是让内联视图保 持独立。这将导致选择更加有效的索引访问操作且需要处理的行更少(26 vs 104)。例子使用的是小表，但可以设想如果你真在查询中涉及有大表将产生多少工作。合并视图的变换使得整体上计划运行更加优化。
    The misconception that an in-line or normal view will be considered first and separately from the
rest of the query often comes from our education about execution order in mathematics.  Let’s consider
the following examples:  
     对内联视图或者普通视图首先被考虑且独立于查询的其它部分的误解通常来自于我们在数学计算顺序的教育经历。让我们考虑下面的例子：
6 + 4 / 2 = 8  
(6 + 4) / 2 = 5
    The parenthesis in the second example cause the addition to happen first, whereas in the first
example the division would happen first based on the rules of precedence order.  We are trained to know
that when we use parenthesis, that action will happen first.  But the SQL language doesn’t follow the
same rules that mathematical expressions do.   Using parenthesis to set a query block apart from another
does not in any way guarantee that block will be executed separately or first. If you have written your
statement to include an in-line view because you intend for that view to be considered separately, you
may need to add the NO_MERGE hint to that query block to prevent it from being rewritten.  As a matter of fact, using the NO_MERGE hint is how I was able to produce the non-merged plan in Listing 2-4.  With this hint, I was able to tell the query transformer that I wanted the o_view query block to be considered
independently from the outer query block.  The query using the hint actually looked like this:
    在第二个例子中的括号使得加法首先运算，而在第一例子中除法将先于加法，基于计算的优先顺序。我们(被教育而)知道当我们使用括号，其中的动作将首先发生。但是SQL语言不遵循数学表达式那套规则。 使用括号来设置查询块而与其他部分分隔开来的做法不能保证块独立或首先运行。 如果你所写的语句中包含内联视图，因为你意图视图被分别考虑，你可能需要加NO_MERGE在查询块中防止它被重写。 事实上，我就是使用NO_MERGE提示生成列表2-4中非合并计划的。使用这个提示，我能告诉查询变换我想让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;
 
    There are some conditions that, if present, will also prevent view merging from occurring. If a query
block contains analytic or aggregate functions, set operations (such as UNION, INTERSECT, MINUS), an ORDER BY clause, or uses ROWNUM, view merging will be prohibited or limited. Even if some of these conditions are present, you can force view merging to take place by using the MERGE hint.   If you force view merging to occur by using the hint, you must make sure that the query result set is still correct after the merge.   If view merging was not going to occur, it was likely due to the fact that the merge might cause the query result to be different.  By using the hint, you are indicating the merge will not affect the answer.  Listing 2-5 shows a statement with an aggregate function that does not view merge and how the use of a MERGE hint can force view merging to occur.
    有些条件，如果存在，也将阻止视图合并的发生。如果一查询块包含分析或聚合函数，集合操作(如UNION, INTERSECT, MINUS)，ORDER BY子句，或使用ROWNUM，视图合并将被禁止或限制。即使这些条件中的一些存在，你能使用MERGE 提示强制视图合并发生。 如果你使用提示强制视图合并发生，你必须确保在合并后查询结果集依然正确。 如果视图合并没有发生，很可能由于事实上合并可能导致不同的查询结果。通过使用提示，你指明合并将不会影响 结果。列表2-5展示了一带有聚合函数的语句没有视图合并，而如何使用MERGE提示强制视图合并发生。
    Listing 2-5. The MERGE Hint
SQL> SELECT e1.last_name, e1.salary, v.avg_salary
  2    FROM employees e1,
  3      (SELECT department_id, avg(salary) avg_salary
  4         FROM employees e2
  5         GROUP BY department_id) v
  6    WHERE e1.department_id = v.department_id AND e1.salary > v.avg_salary;
 
...
38 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2695105989
 
----------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Rows  | Bytes  | Cost (%CPU)  | Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |    17   |   697 |     8  (25)      | 00:00:01 |
|*  1 |   HASH JOIN                       |                   |    17   |   697 |     8  (25)      | 00:00:01 |
|   2 |      VIEW                            |                   |    11   |   286 |     4  (25)      | 00:00:01 |
|   3 |         HASH GROUP BY           |                   |    11   |    77 |     4  (25)      | 00:00:01 |
|   4 |           TABLE ACCESS FULL   | EMPLOYEES  |   107   |   749 |     3   (0)      | 00:00:01 |
|   5 |       TABLE ACCESS FULL       | EMPLOYEES  |   107   |  1605 |     3   (0)      | 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E1"."DEPARTMENT_ID"="V"."DEPARTMENT_ID")
       filter("E1"."SALARY">"V"."AVG_SALARY")
SQL> SELECT /*+ MERGE(v) */ e1.last_name, e1.salary, v.avg_salary
  2    FROM employees e1,
  3      (SELECT department_id, avg(salary) avg_salary
  4         FROM employees e2
  5         GROUP BY department_id) v
  6    WHERE e1.department_id = v.department_id AND e1.salary > v.avg_salary;
 
...
38 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3553954154
----------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes   | Cost (%CPU)    | Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |   165   |  5610   |     8  (25)        | 00:00:01 |
|*  1 |   FILTER                            |                  |           |           |                      |              |
|   2 |      HASH GROUP BY             |                  |   165   |  5610  |     8  (25)         | 00:00:01 |
|*  3 |        HASH JOIN                 |                  |  3296   |   109K |     7  (15)        | 00:00:01 |
|   4 |           TABLE ACCESS FULL  | EMPLOYEES |   107    |  2889  |     3   (0)         | 00:00:01 |
|   5 |           TABLE ACCESS FULL  | EMPLOYEES |   107    |   749   |     3   (0)        | 00:00:01 |
----------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("E1"."SALARY">SUM("SALARY")/COUNT("SALARY"))
   3 - access("E1"."DEPARTMENT_ID"="DEPARTMENT_ID")
    View merging behavior is controlled by the hidden parameter _complex_view_merging that defaults
to TRUE in version 9 and above.  Starting in version 10, transformed queries are reviewed by the optimizer
and the costs of both the merged and non-merged plans are evaluated.  The optimizer will then choose
the plan that is the least costly. 
    试图合并行为被隐式参数_complex_view_merging所控制，默认值在版本9之前是TRUE。至10起，查询变换由优化器检查，合并和非合并的成本都会被评估。优化器将选择最少成本的计划。