Oracle 自动生成的视图VM_NSO_1
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了Oracle 自动生成的视图VM_NSO_1，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含11268字，纯文字阅读大概需要17分钟。
内容图文
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 本文系统来源：http://www.cnblogs.com/lxl57610/p/7464117.html
内容总结
以上是互联网集市为您收集整理的Oracle 自动生成的视图VM_NSO_1全部内容，希望文章能够帮你解决Oracle 自动生成的视图VM_NSO_1所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】