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
appea
rs
on the right side of a semi- or anti-join.

3)
The view
con
tains
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博客 ” ，链接：http://blog.itpub.net/31347199/viewspace-2120530/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31347199/viewspace-2120530/

今天的文章View Merging分享到此就结束了，感谢您的阅读。

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至举报，一经查实，本站将立刻删除。
如需转载请保留出处：https://bianchenghao.cn/6407.html

View Merging