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

SELECT to_char(wmsys.wm_concat(a.TABLE_NAME))

FROM user_tables a, dba_objects b

WHERE a.TABLE_NAME = b.OBJECT_NAME

AND 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 |

-------------------------------------------------------------------------------------------------------