Oracle 自动生成的视图VM_NSO_1

  Table created.   SQL> create table test_jerry2 as select * from dba_objects;   Table created.   SQL> select count(*) from test_jerry where object_id not in (select max(object_id) from test_jerry2 group by owner);     Execution Plan ---------------------------------------------------------- Plan hash value: 3525080607   ------------------------------------------------------------------------------------------------ | Id  | Operation                | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | ------------------------------------------------------------------------------------------------ |   0 | SELECT STATEMENT         |             |     1 |    26 |       |   908   (1)| 00:00:11 | |   1 |  SORT AGGREGATE          |             |     1 |    26 |       |            |          | |*  2 |   HASH JOIN RIGHT ANTI NA|             | 89846 |  2281K|  2144K|   908   (1)| 00:00:11 | |   3 |    VIEW                  | VW_NSO_1    | 87509 |  1110K|       |   349   (1)| 00:00:05 | |   4 |     HASH GROUP BY        |             | 87509 |  2563K|       |   349   (1)| 00:00:05 | |   5 |      TABLE ACCESS FULL   | TEST_JERRY2 | 87509 |  2563K|       |   347   (1)| 00:00:05 | |   6 |    TABLE ACCESS FULL     | TEST_JERRY  | 89847 |  1140K|       |   347   (1)| 00:00:05 | ------------------------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      2 - access("OBJECT_ID"="MAX(OBJECT_ID)")   Note -----    - dynamic sampling used for this statement (level=2)     Statistics ----------------------------------------------------------           0  recursive calls           0  db block gets        2490  consistent gets        2484  physical reads           0  redo size         528  bytes sent via SQL*Net to client         524  bytes received via SQL*Net from client           2  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)           1  rows processed

 

 

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

SQL> select count(*) from test_jerry where object_id not in (select max(object_id) from test_jerry2);     Execution Plan ---------------------------------------------------------- Plan hash value: 3071647562   ------------------------------------------------------------------------------------ | Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------------ |   0 | SELECT STATEMENT     |             |     1 |    13 |   694   (1)| 00:00:09 | |   1 |  SORT AGGREGATE      |             |     1 |    13 |            |          | |*  2 |   TABLE ACCESS FULL  | TEST_JERRY  |  4492 | 58396 |   347   (1)| 00:00:05 | |   3 |    SORT AGGREGATE    |             |     1 |    13 |            |          | |   4 |     TABLE ACCESS FULL| TEST_JERRY2 | 87509 |  1110K|   347   (1)| 00:00:05 | ------------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      2 - filter("OBJECT_ID"<> (SELECT MAX("OBJECT_ID") FROM "TEST_JERRY2"               "TEST_JERRY2"))   Note -----    - dynamic sampling used for this statement (level=2)     Statistics ----------------------------------------------------------           7  recursive calls           0  db block gets        2629  consistent gets        2484  physical reads           0  redo size         528  bytes sent via SQL*Net to client         524  bytes received via SQL*Net from client           2  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)           1  rows processed

 

 

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

SQL> select count(*) from test_jerry where object_id in (select object_id from test_jerry2 where owner=‘SYS‘ minus select object_id from test_jerry where owner=‘SCOTT‘);     Execution Plan ---------------------------------------------------------- Plan hash value: 773093838   ---------------------------------------------------------------------------------------------- | Id  | Operation              | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | ---------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT       |             |     1 |    26 |       |  1307   (1)| 00:00:16 | |   1 |  SORT AGGREGATE        |             |     1 |    26 |       |            |          | |*  2 |   HASH JOIN            |             | 32153 |   816K|       |  1307   (1)| 00:00:16 | |   3 |    VIEW                | VW_NSO_1    | 32153 |   408K|       |   960   (1)| 00:00:12 | |   4 |     MINUS              |             |       |       |       |            |          | |   5 |      SORT UNIQUE       |             | 32153 |   941K|  1272K|            |          | |*  6 |       TABLE ACCESS FULL| TEST_JERRY2 | 32153 |   941K|       |   347   (1)| 00:00:05 | |   7 |      SORT UNIQUE       |             |    14 |   420 |       |            |          | |*  8 |       TABLE ACCESS FULL| TEST_JERRY  |    14 |   420 |       |   347   (1)| 00:00:05 | |   9 |    TABLE ACCESS FULL   | TEST_JERRY  | 89847 |  1140K|       |   347   (1)| 00:00:05 | ----------------------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      2 - access("OBJECT_ID"="OBJECT_ID")    6 - filter("OWNER"=‘SYS‘)    8 - filter("OWNER"=‘SCOTT‘)   Note -----    - dynamic sampling used for this statement (level=2)     Statistics ----------------------------------------------------------          93  recursive calls           0  db block gets        4691  consistent gets        3726  physical reads           0  redo size         528  bytes sent via SQL*Net to client         524  bytes received via SQL*Net from client           2  SQL*Net roundtrips to/from client          12  sorts (memory)           0  sorts (disk)           1  rows processed

 

 

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

SQL> select count(*) from test_jerry where object_id in (select object_id from test_jerry2 where owner=‘SYS‘ union all select object_id from test_jerry where owner=‘SCOTT‘);     Execution Plan ---------------------------------------------------------- Plan hash value: 1173723582   -------------------------------------------------------------------------------------- | Id  | Operation              | Name        | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT       |             |     1 |    26 |  1041   (1)| 00:00:13 | |   1 |  SORT AGGREGATE        |             |     1 |    26 |            |          | |*  2 |   HASH JOIN            |             | 32167 |   816K|  1041   (1)| 00:00:13 | |   3 |    VIEW                | VW_NSO_1    | 32167 |   408K|   694   (1)| 00:00:09 | |   4 |     HASH UNIQUE        |             | 32167 |   942K|   694   (1)| 00:00:09 | |   5 |      UNION-ALL         |             |       |       |            |          | |*  6 |       TABLE ACCESS FULL| TEST_JERRY2 | 32153 |   941K|   347   (1)| 00:00:05 | |*  7 |       TABLE ACCESS FULL| TEST_JERRY  |    14 |   420 |   347   (1)| 00:00:05 | |   8 |    TABLE ACCESS FULL   | TEST_JERRY  | 89847 |  1140K|   347   (1)| 00:00:05 | --------------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      2 - access("OBJECT_ID"="OBJECT_ID")    6 - filter("OWNER"=‘SYS‘)    7 - filter("OWNER"=‘SCOTT‘)   Note -----    - dynamic sampling used for this statement (level=2)     Statistics ----------------------------------------------------------          82  recursive calls           0  db block gets        4669  consistent gets        3726  physical reads           0  redo size         527  bytes sent via SQL*Net to client         524  bytes received via SQL*Net from client           2  SQL*Net roundtrips to/from client           8  sorts (memory)           0  sorts (disk)           1  rows processed

 

 

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

SQL> select count(*) from test_jerry where object_id in (SELECT LEVEL FROM DUAL CONNECT BY LEVEL<100);       Execution Plan ---------------------------------------------------------- Plan hash value: 3708743834   -------------------------------------------------------------------------------------------------------- | Id  | Operation                                 | Name       | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT                          |            |     1 |    26 |   350   (1)| 00:00:05 | |   1 |  SORT AGGREGATE                           |            |     1 |    26 |            |          | |*  2 |   HASH JOIN                               |            |     1 |    26 |   350   (1)| 00:00:05 | |   3 |    VIEW                                   | VW_NSO_1   |     1 |    13 |     3  (34)| 00:00:01 | |   4 |     HASH UNIQUE                           |            |     1 |       |     3  (34)| 00:00:01 | |*  5 |      CONNECT BY WITHOUT FILTERING (UNIQUE)|            |       |       |            |          | |   6 |       FAST DUAL                           |            |     1 |       |     2   (0)| 00:00:01 | |   7 |    TABLE ACCESS FULL                      | TEST_JERRY | 89847 |  1140K|   347   (1)| 00:00:05 | --------------------------------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      2 - access("OBJECT_ID"="LEVEL")    5 - filter(LEVEL<100)   Note -----    - dynamic sampling used for this statement (level=2)     Statistics ----------------------------------------------------------           4  recursive calls           0  db block gets        1315  consistent gets        1242  physical reads           0  redo size         526  bytes sent via SQL*Net to client         524  bytes received via SQL*Net from client           2  SQL*Net roundtrips to/from client           1  sorts (memory)           0  sorts (disk)           1  rows processed

 

可以从上面的sql得出一个简单的结论，当子查询中出现max，rownum，group by，union all，minus，intersect等聚合函数的时候，Oracle就会自动把子查询转换成视图VM_NSO_X，其实在Oracle的子查询中如果出现上面的几种情况，也就限制了view merge，就无法对视图进行merge