有时候优化sql的时候，在执行计划中看到有VM_NSO_X的视图，在Oracle定义中，可以吧NSO理解为nested subquery optimizing，功能就是把in转换为join，把not in转换为anti join等，当然转换的时候有一定的限制。下面我们来简单看下会生成VM_NSO_1视图的几个例子
1. 创建2个测试表
|
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
|
SQL> create table test_jerry as select * from dba_objects;
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。如果需要去掉CBO生成VM_NSO_1，只需要在子查询中加一个no_unnest限制，这样CBO就会走filter了。下面看一个加了no_unnest的执行计划
|
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
|
SQL> select count(*) from test_jerry where object_id in (SELECT /*+ no_unnest() */ LEVEL FROM DUAL t CONNECT BY LEVEL<100);
Execution Plan
----------------------------------------------------------
Plan hash value: 2000702637
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 5963 (1)| 00:01:12 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | FILTER | | | | | |
| 3 | TABLE ACCESS FULL | TEST_JERRY | 89847 | 1140K| 347 (1)| 00:00:05 |
|* 4 | FILTER | | | | | |
|* 5 | CONNECT BY WITHOUT FILTERING (UNIQUE)| | | | | |
| 6 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "SYS"."DUAL" "T" WHERE LEVEL=:B1 CONNECT
BY LEVEL<100))
4 - filter(LEVEL=:B1)
5 - filter(LEVEL<100)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
1385 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
86968 sorts (memory)
0 sorts (disk)
1 rows processed
|
原文：http://www.cnblogs.com/lxl57610/p/7464117.html