===================================================== oracle 内联视图优化，视图合并的抉择 内联视图in-line view，就是sql中from后面有select子查询，或者sql中包含有用create view创建的视图，cbo可能会将内联视图或者视图展开，进行相应的等价改写，这
=====================================================
oracle 内联视图优化，视图合并的抉择
内联视图in-line view，就是sql中from后面有select子查询，或者sql中包含有用create view创建的视图，cbo可能会将内联视图或者视图展开，进行相应的等价改写，这种就叫视图合并。直接看一个sql的执行计划
1. user_tables和dba_objects都是静态数据字典，我们来看下sql的执行计划
select to_char(wmsys.wm_concat(a.table_name))from user_tables a, dba_objects bwhere a.table_name = b.object_nameand b.owner = 'scott'and b.object_type = 'table';执行计划----------------------------------------------------------plan hash value: 555706832-------------------------------------------------------------------------------------------------------| id | operation | name | rows | bytes | cost (%cpu)| time |-------------------------------------------------------------------------------------------------------| 0 | select statement | | 1 | 190 | 1750 (1)| 00:00:22 || 1 | sort aggregate | | 1 | 190 | | ||* 2 | hash join right outer | | 2425 | 449k| 1750 (1)| 00:00:22 || 3 | table access full | seg$ | 5832 | 64152 | 38 (0)| 00:00:01 ||* 4 | hash join right outer | | 2385 | 416k| 1711 (1)| 00:00:21 || 5 | index full scan | i_user2 | 93 | 372 | 1 (0)| 00:00:01 ||* 6 | hash join outer | | 2385 | 407k| 1710 (1)| 00:00:21 ||* 7 | hash join outer | | 2385 | 388k| 1662 (1)| 00:00:20 ||* 8 | hash join | | 2385 | 377k| 1614 (1)| 00:00:20 || 9 | table access full | ts$ | 7 | 21 | 3 (0)| 00:00:01 || 10 | nested loops | | 2385 | 370k| 1611 (1)| 00:00:20 ||* 11 | hash join | | 2385 | 300k| 1517 (1)| 00:00:19 || 12 | view | dba_objects | 2359 | 58975 | 1313 (1)| 00:00:16 || 13 | union-all | | | | | ||* 14 | table access by index rowid | sum$ | 1 | 9 | 1 (0)| 00:00:01 ||* 15 | index unique scan | i_sum$_1 | 1 | | 0 (0)| 00:00:01 ||* 16 | filter | | | | | ||* 17 | hash join | | 25 | 3050 | 48 (3)| 00:00:01 || 18 | nested loops | | 25 | 2500 | 46 (0)| 00:00:01 || 19 | table access by index rowid| user$ | 1 | 17 | 1 (0)| 00:00:01 ||* 20 | index unique scan | i_user1 | 1 | | 0 (0)| 00:00:01 ||* 21 | table access by index rowid| obj$ | 25 | 2075 | 45 (0)| 00:00:01 ||* 22 | index range scan | i_obj5 | 25 | | 27 (0)| 00:00:01 || 23 | index full scan | i_user2 | 93 | 2046 | 1 (0)| 00:00:01 ||* 24 | table access by index rowid | ind$ | 1 | 8 | 2 (0)| 00:00:01 ||* 25 | index unique scan | i_ind1 | 1 | | 1 (0)| 00:00:01 || 26 | nested loops | | 1 | 29 | 2 (0)| 00:00:01 ||* 27 | index full scan | i_user2 | 1 | 20 | 1 (0)| 00:00:01 ||* 28 | index range scan | i_obj4 | 1 | 9 | 1 (0)| 00:00:01 ||* 29 | filter | | | | | || 30 | nested loops | | 1 | 96 | 1 (0)| 00:00:01 || 31 | table access by index rowid | user$ | 1 | 17 | 1 (0)| 00:00:01 ||* 32 | index unique scan | i_user1 | 1 | | 0 (0)| 00:00:01 ||* 33 | index range scan | i_link1 | 1 | 79 | 0 (0)| 00:00:01 || 34 | merge join cartesian | | 2530 | 256k| 203 (2)| 00:00:03 ||* 35 | hash join | | 1 | 68 | 1 (100)| 00:00:01 ||* 36 | fixed table full | x$ksppi | 1 | 55 | 0 (0)| 00:00:01 || 37 | fixed table full | x$ksppcv | 100 | 1300 | 0 (0)| 00:00:01 || 38 | buffer sort | | 2530 | 91080 | 203 (2)| 00:00:03 ||* 39 | table access full | obj$ | 2530 | 91080 | 203 (2)| 00:00:03 ||* 40 | table access cluster | tab$ | 1 | 30 | 1 (0)| 00:00:01 ||* 41 | index unique scan | i_obj# | 1 | | 0 (0)| 00:00:01 || 42 | index fast full scan | i_obj1 | 73384 | 358k| 47 (0)| 00:00:01 || 43 | index fast full scan | i_obj1 | 73384 | 573k| 47 (0)| 00:00:01 |-------------------------------------------------------------------------------------------------------
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
selectto_char(wmsys.wm_concat(a.table_name))
fromuser_tablesa,dba_objectsb
wherea.table_name=b.object_name
andb.owner='scott'
andb.object_type='table';
执行计划
----------------------------------------------------------
planhashvalue:555706832
-------------------------------------------------------------------------------------------------------
|id |operation |name |rows |bytes|cost(%cpu)|time |
-------------------------------------------------------------------------------------------------------
| 0|selectstatement | | 1| 190| 1750 (1)|00:00:22|
| 1| sortaggregate | | 1| 190| | |
|* 2| hashjoinrightouter | | 2425| 449k| 1750 (1)|00:00:22|
| 3| tableaccessfull |seg$ | 5832|64152| 38 (0)|00:00:01|
|* 4| hashjoinrightouter | | 2385| 416k| 1711 (1)|00:00:21|
| 5| indexfullscan |i_user2 | 93| 372| 1 (0)|00:00:01|
|* 6| hashjoinouter | | 2385| 407k| 1710 (1)|00:00:21|
|* 7| hashjoinouter | | 2385| 388k| 1662 (1)|00:00:20|
|* 8| hashjoin | | 2385| 377k| 1614 (1)|00:00:20|
| 9| tableaccessfull |ts$ | 7| 21| 3 (0)|00:00:01|
| 10| nestedloops | | 2385| 370k| 1611 (1)|00:00:20|
|*11| hashjoin | | 2385| 300k| 1517 (1)|00:00:19|
| 12| view |dba_objects| 2359|58975| 1313 (1)|00:00:16|
| 13| union-all | | | | | |
|*14| tableaccessbyindexrowid |sum$ | 1| 9| 1 (0)|00:00:01|
|*15| indexuniquescan |i_sum$_1 | 1| | 0 (0)|00:00:01|
|*16| filter | | | | | |
|*17| hashjoin | | 25| 3050| 48 (3)|00:00:01|
| 18| nestedloops | | 25| 2500| 46 (0)|00:00:01|
| 19| tableaccessbyindexrowid|user$ | 1| 17| 1 (0)|00:00:01|
|*20| indexuniquescan |i_user1 | 1| | 0 (0)|00:00:01|
|*21| tableaccessbyindexrowid|obj$ | 25| 2075| 45 (0)|00:00:01|
|*22| indexrangescan |i_obj5 | 25| | 27 (0)|00:00:01|
| 23| indexfullscan |i_user2 | 93| 2046| 1 (0)|00:00:01|
|*24| tableaccessbyindexrowid |ind$ | 1| 8| 2 (0)|00:00:01|
|*25| indexuniquescan |i_ind1 | 1| | 1 (0)|00:00:01|
| 26| nestedloops | | 1| 29| 2 (0)|00:00:01|
|*27| indexfullscan |i_user2 | 1| 20| 1 (0)|00:00:01|
|*28| indexrangescan |i_obj4 | 1| 9| 1 (0)|00:00:01|
|*29| filter | | | | | |
| 30| nestedloops | | 1| 96| 1 (0)|00:00:01|
| 31| tableaccessbyindexrowid|user$ | 1| 17| 1 (0)|00:00:01|
|*32| indexuniquescan |i_user1 | 1| | 0 (0)|00:00:01|
|*33| indexrangescan |i_link1 | 1| 79| 0 (0)|00:00:01|
| 34| mergejoincartesian | | 2530| 256k| 203 (2)|00:00:03|
|*35| hashjoin | | 1| 68| 1(100)|00:00:01|
|*36| fixedtablefull |x$ksppi | 1| 55| 0 (0)|00:00:01|
| 37| fixedtablefull |x$ksppcv | 100| 1300| 0 (0)|00:00:01|
| 38| buffersort | | 2530|91080| 203 (2)|00:00:03|
|*39| tableaccessfull |obj$ | 2530|91080| 203 (2)|00:00:03|
|*40| tableaccesscluster |tab$ | 1| 30| 1 (0)|00:00:01|
|*41| indexuniquescan |i_obj# | 1| | 0 (0)|00:00:01|
| 42| indexfastfullscan |i_obj1 |73384| 358k| 47 (0)|00:00:01|
| 43| indexfastfullscan |i_obj1 |73384| 573k| 47 (0)|00:00:01|
-------------------------------------------------------------------------------------------------------
可以看到id=12这一步有一个view关键字，这一步正好是提取dba_objects视图的数据，然后此表和id=34这一步进行hash join，但hash join的表并不是sql当中的user_tables，而且在整个执行计划当中都找不到这个视图的信息，此视图被展开了。但这种情况我们是不需要展开的
2. 既然不需要展开，我们直接使用hint禁止视图合并
sql> select /*+ no_merge(a) */ to_char(wmsys.wm_concat(a.table_name))2 from user_tables a, dba_objects b3 where a.table_name = b.object_name4 and b.owner = 'scott'5 and b.object_type = 'table';执行计划----------------------------------------------------------plan hash value: 3412902540--------------------------------------------------------------------------------------------------| id | operation | name | rows | bytes | cost (%cpu)| time |--------------------------------------------------------------------------------------------------| 0 | select statement | | 1 | 50 | 1756 (1)| 00:00:22 || 1 | sort aggregate | | 1 | 50 | | ||* 2 | hash join | | 2359 | 115k| 1756 (1)| 00:00:22 || 3 | view | dba_objects | 2359 | 58975 | 1313 (1)| 00:00:16 || 4 | union-all | | | | | ||* 5 | table access by index rowid | sum$ | 1 | 9 | 1 (0)| 00:00:01 ||* 6 | index unique scan | i_sum$_1 | 1 | | 0 (0)| 00:00:01 ||* 7 | filter | | | | | ||* 8 | hash join | | 25 | 3050 | 48 (3)| 00:00:01 || 9 | nested loops | | 25 | 2500 | 46 (0)| 00:00:01 || 10 | table access by index rowid| user$ | 1 | 17 | 1 (0)| 00:00:01 ||* 11 | index unique scan | i_user1 | 1 | | 0 (0)| 00:00:01 ||* 12 | table access by index rowid| obj$ | 25 | 2075 | 45 (0)| 00:00:01 ||* 13 | index range scan | i_obj5 | 25 | | 27 (0)| 00:00:01 || 14 | index full scan | i_user2 | 93 | 2046 | 1 (0)| 00:00:01 ||* 15 | table access by index rowid | ind$ | 1 | 8 | 2 (0)| 00:00:01 ||* 16 | index unique scan | i_ind1 | 1 | | 1 (0)| 00:00:01 || 17 | nested loops | | 1 | 29 | 2 (0)| 00:00:01 ||* 18 | index full scan | i_user2 | 1 | 20 | 1 (0)| 00:00:01 ||* 19 | index range scan | i_obj4 | 1 | 9 | 1 (0)| 00:00:01 ||* 20 | filter | | | | | || 21 | nested loops | | 1 | 96 | 1 (0)| 00:00:01 || 22 | table access by index rowid | user$ | 1 | 17 | 1 (0)| 00:00:01 ||* 23 | index unique scan | i_user1 | 1 | | 0 (0)| 00:00:01 ||* 24 | index range scan | i_link1 | 1 | 79 | 0 (0)| 00:00:01 || 25 | view | user_tables | 2573 | 64325 | 442 (2)| 00:00:06 ||* 26 | hash join right outer | | 2573 | 414k| 442 (2)| 00:00:06 || 27 | table access full | seg$ | 5832 | 64152 | 38 (0)| 00:00:01 ||* 28 | hash join right outer | | 2530 | 380k| 403 (2)| 00:00:05 || 29 | index full scan | i_user2 | 93 | 372 | 1 (0)| 00:00:01 ||* 30 | hash join outer | | 2530 | 370k| 402 (2)| 00:00:05 ||* 31 | hash join outer | | 2530 | 350k| 354 (2)| 00:00:05 ||* 32 | hash join | | 2530 | 338k| 306 (2)| 00:00:04 || 33 | table access full | ts$ | 7 | 21 | 3 (0)| 00:00:01 || 34 | nested loops | | 2530 | 331k| 302 (1)| 00:00:04 || 35 | merge join cartesian | | 2530 | 256k| 203 (2)| 00:00:03 ||* 36 | hash join | | 1 | 68 | 1 (100)| 00:00:01 ||* 37 | fixed table full | x$ksppi | 1 | 55 | 0 (0)| 00:00:01 || 38 | fixed table full | x$ksppcv | 100 | 1300 | 0 (0)| 00:00:01 || 39 | buffer sort | | 2530 | 91080 | 203 (2)| 00:00:03 ||* 40 | table access full | obj$ | 2530 | 91080 | 203 (2)| 00:00:03 ||* 41 | table access cluster | tab$ | 1 | 30 | 1 (0)| 00:00:01 ||* 42 | index unique scan | i_obj# | 1 | | 0 (0)| 00:00:01 || 43 | index fast full scan | i_obj1 | 73384 | 358k| 47 (0)| 00:00:01 || 44 | index fast full scan | i_obj1 | 73384 | 573k| 47 (0)| 00:00:01 |--------------------------------------------------------------------------------------------------
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
sql>select/*+ no_merge(a) */to_char(wmsys.wm_concat(a.table_name))
2 fromuser_tablesa,dba_objectsb
3 wherea.table_name=b.object_name
4 andb.owner='scott'
5 andb.object_type ='table';
执行计划
----------------------------------------------------------
planhashvalue:3412902540
--------------------------------------------------------------------------------------------------
|id |operation |name |rows |bytes|cost(%cpu)|time |
--------------------------------------------------------------------------------------------------
| 0|selectstatement | | 1| 50| 1756 (1)|00:00:22|
| 1| sortaggregate | | 1| 50| | |
|* 2| hashjoin | | 2359| 115k| 1756 (1)|00:00:22|
| 3| view |dba_objects| 2359|58975| 1313 (1)|00:00:16|
| 4| union-all | | | | | |
|* 5| tableaccessbyindexrowid |sum$ | 1| 9| 1 (0)|00:00:01|
|* 6| indexuniquescan |i_sum$_1 | 1| | 0 (0)|00:00:01|
|* 7| filter | | | | | |
|* 8| hashjoin | | 25| 3050| 48 (3)|00:00:01|
| 9| nestedloops | | 25| 2500| 46 (0)|00:00:01|
| 10| tableaccessbyindexrowid|user$ | 1| 17| 1 (0)|00:00:01|
|*11| indexuniquescan |i_user1 | 1| | 0 (0)|00:00:01|
|*12| tableaccessbyindexrowid|obj$ | 25| 2075| 45 (0)|00:00:01|
|*13| indexrangescan |i_obj5 | 25| | 27 (0)|00:00:01|
| 14| indexfullscan |i_user2 | 93| 2046| 1 (0)|00:00:01|
|*15| tableaccessbyindexrowid |ind$ | 1| 8| 2 (0)|00:00:01|
|*16| indexuniquescan |i_ind1 | 1| | 1 (0)|00:00:01|
| 17| nestedloops | | 1| 29| 2 (0)|00:00:01|
|*18| indexfullscan |i_user2 | 1| 20| 1 (0)|00:00:01|
|*19| indexrangescan |i_obj4 | 1| 9| 1 (0)|00:00:01|
|*20| filter | | | | | |
| 21| nestedloops | | 1| 96| 1 (0)|00:00:01|
| 22| tableaccessbyindexrowid|user$ | 1| 17| 1 (0)|00:00:01|
|*23| indexuniquescan |i_user1 | 1| | 0 (0)|00:00:01|
|*24| indexrangescan |i_link1 | 1| 79| 0 (0)|00:00:01|
| 25| view |user_tables| 2573|64325| 442 (2)|00:00:06|
|*26| hashjoinrightouter | | 2573| 414k| 442 (2)|00:00:06|
| 27| tableaccessfull |seg$ | 5832|64152| 38 (0)|00:00:01|
|*28| hashjoinrightouter | | 2530| 380k| 403 (2)|00:00:05|
| 29| indexfullscan |i_user2 | 93| 372| 1 (0)|00:00:01|
|*30| hashjoinouter | | 2530| 370k| 402 (2)|00:00:05|
|*31| hashjoinouter | | 2530| 350k| 354 (2)|00:00:05|
|*32| hashjoin | | 2530| 338k| 306 (2)|00:00:04|
| 33| tableaccessfull |ts$ | 7| 21| 3 (0)|00:00:01|
| 34| nestedloops | | 2530| 331k| 302 (1)|00:00:04|
| 35| mergejoincartesian | | 2530| 256k| 203 (2)|00:00:03|
|*36| hashjoin | | 1| 68| 1(100)|00:00:01|
|*37| fixedtablefull |x$ksppi | 1| 55| 0 (0)|00:00:01|
| 38| fixedtablefull |x$ksppcv | 100| 1300| 0 (0)|00:00:01|
| 39| buffersort | | 2530|91080| 203 (2)|00:00:03|
|*40| tableaccessfull |obj$ | 2530|91080| 203 (2)|00:00:03|
|*41| tableaccesscluster |tab$ | 1| 30| 1 (0)|00:00:01|
|*42| indexuniquescan |i_obj# | 1| | 0 (0)|00:00:01|
| 43| indexfastfullscan |i_obj1 |73384| 358k| 47 (0)|00:00:01|
| 44| indexfastfullscan |i_obj1 |73384| 573k| 47 (0)|00:00:01|
--------------------------------------------------------------------------------------------------
现在可以在执行计划中看到id=3和id=25这两步都是视图，通过hash join连接。
疑问：为什么这里不需要视图合并？
答曰：如果不视图合并，那整个视图就会当成一整块，在sql执行的时候，这个视图就是一个结果集，然后再去和另一个结果集关联。如果合并了的话，那这个视图就会被拆散，视图里面的关联就会分开run，并不是每次视图合并都是高效的。
在执行计划中，如果看到view关键字，说明视图没有展开，也就是视图没有合并，如果本来sql中有内联视图或者视图，但执行计划中没有看到view关键字，那这个sql就进行了视图合并。
此外还需要注意的是，如果sql中的内联视图有聚合等操作，比如rownum，start with，connect by，union，union all，rollup，cube等，这种内联视图就不能展开，因为内联视图被固化了，碰到这种情况就需要注意，如果内联视图中结果集很大，那sql估计就要改写了，因为这个内联视图会最先执行。
http://www.savedba.com/?p=816============================================
3.1.1.1 内联视图合并
2013-02-25 16:45 黄玮 机械工业出版社 我要评论(0) 字号：t | t
综合评级：
想读(1) 在读(0) 已读(0) 品书斋鉴(0) 已有1人发表书评
《oracle高性能sql引擎剖析：sql优化与调优机制详解》第3章查询转换，在本章中，我们将会了解到以下内容：oracle的逻辑优化技术中，存在哪些启发式查询转换技术，以及它们的具体含义和示例；oracle的逻辑优化技术中，存在哪些基于代价的查询转换技术，以及它们的具体含义和示例。本节为大家介绍内联视图合并。
ad：2014wot全球软件技术峰会北京站 课程视频发布
3.1.1.1 内联视图合并
我们以下面两个执行计划为例，简要说明视图合并技术对执行计划优化的影响，见代码清单3-1。
代码清单3-1 内联视图合并
hellodba.com>exec sql_explain('select /*+no_merge(o)*/* from t_tables t, v_objects_sys o where t.owner=o.owner and t.table_name = object_name and t.tablespace_name = :a and t.table_name like :b and o.status=:c', 'typical'); plan hash value: 3284354748 ---------------------------------------------------------------------------------------------------- | id | operation | name | rows | bytes | cost (%cpu)| time | ---------------------------------------------------------------------------------------------------- | 0 | select statement | | 3 | 840 | 87 (3)| 00:00:01 | |* 1 | hash join | | 3 | 840 | 87 (3)| 00:00:01 | | 2 | table access by index rowid | t_tables | 9 | 1836 | 13 (8)| 00:00:01 | | 3 | bitmap conversion to rowids | | | | | | | 4 | bitmap and | | | | | | | 5 | bitmap conversion from rowids| | | | | | |* 6 | index range scan | t_tables_idx3 | 184 | | 1 (0)| 00:00:01 | | 7 | bitmap conversion from rowids| | | | | | | 8 | sort order by | | | | | | |* 9 | index range scan | t_tables_pk | 184 | | 2 (0)| 00:00:01 | | 10 | view | v_objects_sys | 571 | 43396 | 73 (0)| 00:00:01 | | 11 | table access by index rowid | t_objects | 571 | 47393 | 73 (0)| 00:00:01 | |* 12 | index range scan | t_objects_idx1 | 103 | | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------------- predicate information (identified by operation id): --------------------------------------------------- 1 - access(t.owner&quo