Oracle 内联视图优化，视图合并的抉择
- WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB原创
- 2016-06-07 15:46:041383浏览
===================================================== 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的执行计划
|
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禁止视图合并
|
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 内联视图合并
<ol>
<li><span><span>HELLODBA.COM</span><span>></span><span>exec sql_explain('select /*+no_merge(o)*/* from t_tables t, v_objects_sys o where </span></span></li>
<li>
<span> </span><span>t.owner</span><span>=o.owner and </span><span>t.table_name</span><span> = </span><span>object_name</span><span> and </span><span>t.tablespace_name</span><span> = :A and t.table_name </span>
</li>
<li>
<span> like :B and </span><span>o.status</span><span>=:C', 'TYPICAL'); </span>
</li>
<li><span>Plan hash value: 3284354748 </span></li>
<li><span>---------------------------------------------------------------------------------------------------- </span></li>
<li><span>| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | </span></li>
<li><span>---------------------------------------------------------------------------------------------------- </span></li>
<li><span>| 0 | SELECT STATEMENT | | 3 | 840 | 87 (3)| 00:00:01 | </span></li>
<li><span>|* 1 | HASH JOIN | | 3 | 840 | 87 (3)| 00:00:01 | </span></li>
<li><span>| 2 | TABLE ACCESS BY INDEX ROWID | T_TABLES | 9 | 1836 | 13 (8)| 00:00:01 | </span></li>
<li><span>| 3 | BITMAP CONVERSION TO ROWIDS | | | | | | </span></li>
<li><span>| 4 | BITMAP AND | | | | | | </span></li>
<li><span>| 5 | BITMAP CONVERSION FROM ROWIDS| | | | | | </span></li>
<li><span>|* 6 | INDEX RANGE SCAN | T_TABLES_IDX3 | 184 | | 1 (0)| 00:00:01 | </span></li>
<li><span>| 7 | BITMAP CONVERSION FROM ROWIDS| | | | | | </span></li>
<li><span>| 8 | SORT ORDER BY | | | | | | </span></li>
<li><span>|* 9 | INDEX RANGE SCAN | T_TABLES_PK | 184 | | 2 (0)| 00:00:01 | </span></li>
<li><span>| 10 | VIEW | V_OBJECTS_SYS | 571 | 43396 | 73 (0)| 00:00:01 | </span></li>
<li><span>| 11 | TABLE ACCESS BY INDEX ROWID | T_OBJECTS | 571 | 47393 | 73 (0)| 00:00:01 | </span></li>
<li><span>|* 12 | INDEX RANGE SCAN | T_OBJECTS_IDX1 | 103 | | 3 (0)| 00:00:01 | </span></li>
<li><span>---------------------------------------------------------------------------------------------------- </span></li>
<li><span> </span></li>
<li><span>Predicate Information (identified by operation id): </span></li>
<li><span>--------------------------------------------------- </span></li>
<li><span> 1 - access("T"."OWNER&quo</span></li>
</ol>