Query Transformation-视图篇

CBO处理视图(包括内嵌视图和查询转换生成的视图)，也是有且只有3种处理方式

• view merge
• jppd/fpd
• view当作一个整体

view merge：

The purpose of view merging is to reduce the number of query blocks due to views and inline views by merging several of them together. This query transformation was introduced because, without it, the query optimizer would process each query block separately. When processing query blocks separately, the query optimizer can’t always find an execution plan that is optimal for the SQL statement as a whole. In addition, the query block resulting from view merging might enable further query transformations to be considered.

Recursively process any views referenced by the current view
Either completely merge the view into the referencing query block
OR simply move the definition of the view in-line into the referencing query block

简单来说就是CBO将视图展开到外部查询中去，并且将视图中的where条件也合并到外部查询的where条件中。

view merging分类：

SVM – simple view merging
CVM – complex view merging

简单视图：

simple views that contains Select , Project and Join only (SPJ – select-project-join）
Simple view merging is used for merging plain, select-project-join query blocks.Because of the simplicity of the cases it handles, simple view merging is a heuristic-based query transformation. It can’t be applied to views or inline views that contain constructs like aggregations, set operators, hierarchical queries, the MODEL clause, or subqueries in the SELECT list

只要_simple_view_merging为true并且视图不违反视图合并的限制，就一定会做视图合并。

复杂视图：

complex views containing grouping, aggregation, distinct and outer join
Complex view merging is used for merging query blocks that contain aggregations. It is a cost-based query transformation that can’t be applied to views or inline views that, for example, either appear in hierarchical queries or contain GROUPING SETS, ROLLUP, PIVOT, or MODEL clauses.

需要_complex_view_merging为true并且视图不违反视图合并的限制，而且合并后的cost要小于不视图合并的cost才会做视图合并。

view merge参数与hint：

view merge限制：

Oracle can merge several different types of views:

• Simple view merging, for simple select-project-join views.
• Outer-join view merging for outer-joined views.
• Complex view merging, for distinct and group by views.

There are several reasons why a select-project-join view might not be merged, typically because it is not semantically valid to do so. Some of the reasons a view may not be valid for simple view merging are listed below.

• The view contains constructs other than select, project, join, including:
  • Group by
  • Distinct
  • Outer-join
  • Spreadsheet clause
  • Connect by
  • Set operators
  • Aggregation
• The view appears on the right side of a semi- or anti-join.
• The view contains subqueries in the select list.
• The outer query block contains PL/SQL functions.

some of the reasons a group by or distinct view might not be merged. Aside from cost, there are several other reasons, including:

• The outer query tables do not have a rowid or unique column
• View appears in a connect by query block
• View contains grouping sets, rollup, pivot
• View or outer query block contains spreadsheet clause

对于不能做视图合并的视图，CBO将考虑做谓词推入(JPPD/FPD)，如果外部有关联则是连接谓词推入(JPPD)。

JPPD-join predicate push-down:

The join predicate pushdown (JPPD) transformation allows a view to be joined with index-based nested-loop join method, which may provide a more optimal alternative. In the join predicate pushdown transformation, the view remains a separate query block, but it contains the join predicate, which is pushed down from its containing query block into the view. The view thus becomes correlated and must be evaluated for each row of the outer query block. These pushed-down join predicates, once inside the view, open up new index access paths on the base tables inside the view; this allows the view to be joined with index-based nested-loop join method, thereby enabling the optimizer to select an efficient execution plan.

At the first step, Oracle tries to merge the view. This is called view merging. When the view merging is not possible, Oracle tries to push the predicates into the view. Oracle pushes not only simple predicates, but also join predicates. PUSH_PRED hint controls the join predicate pushing.

JPPD分类：

• JPPD – join predicate push-down，10gR2之后，jppd是基于cost的，不仅需要满足_push_join_predicate=true和连接的视图满足_optimizer_extend_jppd_view_types的类型，且转换后cost要小于不转换的cost。
• OJPPD – old-style (non-cost-based) JPPD，10gR2之前，jppd是启发式转换，现在叫ojppd，也就是不计成本的。10gR2后如果要启用OJPPD，需要关闭_optimizer_cost_based_transformation或者_optimizer_push_pred_cost_based

JPPD参数与hint：

JPPD限制：

允许JPPD的视图由参数_optimizer_extend_jppd_view_types说明

即视图需要满足：

• UNION ALL/UNION view
• Outer-joined view
• Anti-joined view
• Semi-joined view
• DISTINCT view
• GROUP-BY view

视图其他查询转换：

PM – predicate move-around(谓词迁移)

A new type of optimization, called predicate move-around, is introduced. It is shown how this optimization considerably improves the efficiency of evaluating SQL queries that have query graphs with a large number of query blocks (which is a typical situation when queries are defined in terms of multiple views and subqueries). Predicate move-around works by moving predicates across query blocks (in the query graph) that cannot be merged into one block. Predicate move-around is a generalization of and has many advantages over the traditional predicate pushdown. One key advantage arises from the fact that predicate move-around precedes pushdown by pulling predicates up the query graph. As a result, predicates that appear in the query in one part of the graph can be moved around the graph and applied also in other parts of graph.

Moreover, predicate move-around optimization can move a wider class of predicates in a wider class of queries as compared to the standard predicate-pushdown techniques. In addition to the usual comparison and arithmetic predicates, other predicates that can be moved around are the EXISTS and NOT EXISTS clauses, the EXCEPT clause, and functional dependencies. The proposed optimization can also move predicates through aggregation. Moreover, the method can also infer new predicates when existing predicates are moved through aggregation or when certain functional dependencies are known to hold. Finally, the predicate move-around algorithm is easy to implement on top of existing query optimizers.

简单来说就是多个视图的查询中，将某一个视图的谓词条件Pull up出来并且push down到另一个视图中。该转换属于启发式查询转换。

参数：

PM示例：