当前位置：Gxlcms > mysql > Oracle内联视图优化，视图合并的抉择

Oracle内联视图优化，视图合并的抉择

时间：2021-07-01 10:21:17 帮助过：28人阅读

===================================================== 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　内联视图合并

  1. HELLODBA.COM>exec sql_explain('select /*+no_merge(o)*/* from t_tables t, v_objects_sys o where  
  2.              t.owner=o.owner and t.table_name = object_name and t.tablespace_name = :A and t.table_name  
  3.              like :B and o.status=:C', 'TYPICAL');  
  4. Plan hash value: 3284354748  
  5. ----------------------------------------------------------------------------------------------------  
  6. | Id  | Operation                         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |  
  7. ----------------------------------------------------------------------------------------------------  
  8. |   0 | SELECT STATEMENT                  |                |     3 |   840 |    87   (3)| 00:00:01 |  
  9. |*  1 |  HASH JOIN                        |                |     3 |   840 |    87   (3)| 00:00:01 |  
  10. |   2 |   TABLE ACCESS BY INDEX ROWID     | T_TABLES       |     9 |  1836 |    13   (8)| 00:00:01 |  
  11. |   3 |    BITMAP CONVERSION TO ROWIDS    |                |       |       |            |          |  
  12. |   4 |     BITMAP AND                    |                |       |       |            |          |  
  13. |   5 |      BITMAP CONVERSION FROM ROWIDS|                |       |       |            |          |  
  14. |*  6 |       INDEX RANGE SCAN            | T_TABLES_IDX3  |   184 |       |     1   (0)| 00:00:01 |  
  15. |   7 |      BITMAP CONVERSION FROM ROWIDS|                |       |       |            |          |  
  16. |   8 |       SORT ORDER BY               |                |       |       |            |          |  
  17. |*  9 |        INDEX RANGE SCAN           | T_TABLES_PK    |   184 |       |     2   (0)| 00:00:01 |  
  18. |  10 |   VIEW                            | V_OBJECTS_SYS  |   571 | 43396 |    73   (0)| 00:00:01 |  
  19. |  11 |    TABLE ACCESS BY INDEX ROWID    | T_OBJECTS      |   571 | 47393 |    73   (0)| 00:00:01 |  
  20. |* 12 |     INDEX RANGE SCAN              | T_OBJECTS_IDX1 |   103 |       |     3   (0)| 00:00:01 |  
  21. ----------------------------------------------------------------------------------------------------  
  22.  
  23. Predicate Information (identified by operation id):  
  24. ---------------------------------------------------  
  25.    1 - access("T"."OWNER&quo

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！