1.定义：

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.

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

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

2.分类(基于10053)：

SVM - simple view merging
CVM - complex view merging

Oracle can merge several different types of views:
1)Simple view merging, for simple select-project-join views.
2)Outer-join view merging for outer-joined views.
3)Complex view merging, for distinct and group by views.

1)简单视图：

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并且视图不违反视图合并的限制，就一定会做视图合并。

2)复杂视图：

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才会做视图合并。

3.参数：默认都为true

KSPPINM KSPPSTVL KSPPDESC
------------------------------ ---------- -------------------------------------------------------
_complex_view_merging TRUE enable complex view merging
_simple_view_merging TRUE control simple view merging performed by the optimizer

hint：merge/no_merge

4.限制：

svm:

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.
1)The view contains constructs other than select, project, join, including:
Group by
  Distinct
  Outer-join
  Spreadsheet clause
  Connect by
  Set operators
Aggregation
2)The view appears on the right side of a semi- or anti-join.
3)The view contains subqueries in the select list.
4)The outer query block contains PL/SQL functions.

cvm:

some of the reasons a group by or distinct view might not be merged. Aside from cost, there are several other reasons, including:
1)The outer query tables do not have a rowid or unique column
2)View appears in a connect by query block
3)View contains grouping sets, rollup, pivot
4)View or outer query block contains spreadsheet clause

5.自我折腾的测试：

SQL> create table lxy as select rownum a,mod(rownum,2) b from dual connect by rownum<=1000;
Table created.
SQL> create index i on lxy(a);
Index created.
SQL> EXEC dbms_stats.gather_table_stats(ownname => 'SYS', tabname => 'LXY', estimate_percent => dbms_stats.auto_sample_size, CASCADE => true, method_opt => 'for all columns size repeat', no_invalidate => false);
PL/SQL procedure successfully completed.

sql文本

SELECT /*+qb_name(nb)*/*
FROM (SELECT /*+qb_name(sb)*/*
FROM lxy
WHERE b = 1) t
WHERE a = 5;

执行计划

Plan hash value: 2326783758
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| LXY | 1 | 7 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$A772C6DB / LXY@SB
2 - SEL$A772C6DB / LXY@SB
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
INDEX_RS_ASC(@"SEL$A772C6DB" "LXY"@"SB" ("LXY"."A"))
OUTLINE(@"SB")
OUTLINE(@"NB")
MERGE(@"SB")
OUTLINE_LEAF(@"SEL$A772C6DB")
ALL_ROWS
DB_VERSION('11.2.0.4')
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("B"=1)
2 - access("LXY"."A"=5)

这里清楚的看到CBO对查询块SB做了视图合并，将内嵌视图展开并将查询块SB里的谓词与查询块NB里的谓词做了合并形成了新的查询块A772C6DB(系统自动命名)
10053也可以验证：
CVM: Merging SPJ view SB (#0) into NB (#0)
Registered qb: SEL$A772C6DB 0x73342b80 (VIEW MERGE NB; SB)
改写后的语句大致为：SELECT * FROM lxy WHERE b = 1 and WHERE a = 5

实际上该例子就算禁用视图合并，CBO仍然会对该sql进行FPD(filter push-down)下面验证一下:

禁用SVM：

SELECT /*+qb_name(nb) no_merge(t)*/*
FROM (SELECT /*+qb_name(sb)*/*
FROM lxy
WHERE b = 1) t
WHERE a = 5;

Plan hash value: 993270418
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 2 (0)| 00:00:01 |
| 1 | VIEW | | 1 | 26 | 2 (0)| 00:00:01 |
|* 2 | TABLE ACCESS BY INDEX ROWID| LXY | 1 | 7 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | I | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SB / T@NB
2 - SB / LXY@SB
3 - SB / LXY@SB
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
INDEX_RS_ASC(@"SB" "LXY"@"SB" ("LXY"."A"))
NO_ACCESS(@"NB" "T"@"NB")
OUTLINE(@"NB")
OUTLINE(@"SB")
OUTLINE_LEAF(@"NB")
OUTLINE_LEAF(@"SB")
ALL_ROWS
DB_VERSION('11.2.0.4')
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("B"=1)
3 - access("LXY"."A"=5)

这里outline部分没有发现merge说明视图合并已经成功被禁用了，并且在执行计划中也出现了VIEW，那为什么WHERE a = 5仍然能直接作用在表lxy上呢？因为发生了FPD，从10053中可以验证：
FPD: Considering simple filter push in query block SB (#0) --将谓词where a=5推入了查询块SB当中
"LXY"."B"=1 AND "LXY"."A"=5
try to generate transitive predicate from check constraints for query block SB (#0)
finally: "LXY"."B"=1 AND "LXY"."A"=5
改写后的sql大致为：SELECT * from (SELECT * FROM lxy WHERE b = 1 and WHERE a = 5) t

那如果把FPD也禁用了呢？
同时禁用SVM和FPD(hint no_push_pred也可以):

SELECT /*+qb_name(nb) no_merge(t) opt_param('_optimizer_filter_pushdown' 'false')*/*
FROM (SELECT /*+qb_name(sb)*/*
FROM lxy
WHERE b = 1) t
WHERE a = 5;

Plan hash value: 1554672421
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 500 | 13000 | 2 (0)| 00:00:01 |
|* 1 | VIEW | | 500 | 13000 | 2 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| LXY | 500 | 3500 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SB / T@NB
2 - SB / LXY@SB
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"SB" "LXY"@"SB")
NO_ACCESS(@"NB" "T"@"NB")
OUTLINE(@"NB")
OUTLINE(@"SB")
OUTLINE_LEAF(@"NB")
OUTLINE_LEAF(@"SB")
ALL_ROWS
DB_VERSION('11.2.0.4')
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"=5)
2 - filter("B"=1)
这里可以CBO没有对sql做任何改写，where a=5已经无法直接作用在表lxy上了，所以执行计划变成了全表扫描。

ITPub博客

View Merging