===================================================== 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 ||<i style="color:transparent">本文来源gaodai$ma#com搞$$代**码网$</i> 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 |——————————————————————————————————-

喜欢 (0)赏

[搞代码]

分享 (0)