what is VW_NSO_1 view?
VW_NSO_1 我们查看执行计划的时候动态创建的，VM 是ORACLE视图的约定前缀/后缀，在和老外工作的时候，经常可以看到有VW的后缀，它就表示视图，这是习惯。
下面就是某日用品行业老大某某数据仓库的视图，它的后缀是VW
SQL> select view_name from dba_views where view_name like '%VW%';
VIEW_NAME
------------------------------------------------------------
GPOS_MDGC_TSC_ADL_VW
GPOS_MDGC_TSC_STORE_GRP_VW
GPOS_LLCA02_FACT_NT_SPEC_VW
GPOS_LLCA_ADL_VW
GPOS_LLCA_STORE_GRP_VW
GPOS_LLCA_AZ_FACT_VW
GPOS_RODE03_FACT_NATL_SPEC_VW
.....................................................................省略......................................................................
NSO 可以这样理解：nested subquery optimizing ，把in转换为JOIN，把NOT IN转换为 anti join(如果能确保列不为null)
那么什么时候oracle会生成 VW_NSO_1 这样的视图呢，请看下面的几个例子(基于ORACLE10g)：
SQL> select count(*) from t1 where object_id not in (select MAX(object_id) from t2 GROUP BY OWNER);
已用时间: 00: 00: 00.05
执行计划
----------------------------------------------------------
Plan hash value: 350791718
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18 | 32 (13)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 18 | | |
|* 2 | HASH JOIN RIGHT ANTI | | 51176 | 899K| 32 (13)| 00:00:01 |
| 3 | VIEW | VW_NSO_1 | 1 | 13 | 3 (34)| 00:00:01 |
| 4 | HASH GROUP BY | | 1 | 30 | 3 (34)| 00:00:01 |
| 5 | TABLE ACCESS FULL | T2 | 1 | 30 | 2 (0)| 00:00:01 |
| 6 | INDEX FAST FULL SCAN| T1_ID | 51177 | 249K| 27 (4)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"="$nso_col_1")
SQL> select count(*) from t1 where object_id not in (select MAX(object_id) from t2 );
已用时间: 00: 00: 00.01
执行计划
----------------------------------------------------------
Plan hash value: 4201411252
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 29 (7)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | INDEX FAST FULL SCAN | T1_ID | 51176 | 249K| 28 (8)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 13 | | |
| 4 | INDEX FULL SCAN (MIN/MAX)| T2_ID | 1 | 13 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID"<> (SELECT MAX("OBJECT_ID") FROM "T2" "T2"))
SQL> select count(*) from t1 where object_id in (select object_id from t2 where rownum=10);
已用时间: 00: 00: 00.00
执行计划
----------------------------------------------------------
Plan hash value: 3285035028
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18 | 2 (50)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 18 | | |
| 2 | NESTED LOOPS | | 1 | 18 | 2 (50)| 00:00:01 |
| 3 | VIEW | VW_NSO_1 | 1 | 13 | 0 (0)| 00:00:01 |
| 4 | HASH UNIQUE | | 1 | 13 | | |
| 5 | COUNT | | | | | |
|* 6 | FILTER | | | | | |
| 7 | INDEX FULL SCAN| T2_ID | 1 | 13 | 0 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | T1_ID | 1 | 5 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter(ROWNUM=10)
8 - access("OBJECT_ID"="$nso_col_1")
SQL> select count(*) from t1 where object_id in (select object_id from t2 where owner='SYS' UNION ALL select object_id from test where owner='SCOTT');
已用时间: 00: 00: 00.06
执行计划
----------------------------------------------------------
Plan hash value: 3457113806
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18 | 34 (9)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 18 | | |
|* 2 | HASH JOIN | | 140 | 2520 | 34 (9)| 00:00:01 |
| 3 | VIEW | VW_NSO_1 | 137 | 1781 | 5 (0)| 00:00:01 |
| 4 | HASH UNIQUE | | 137 | 1526 | 5 (60)| 00:00:01 |
| 5 | UNION-ALL | | | | | |
|* 6 | TABLE ACCESS FULL| T2 | 1 | 30 | 2 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | XXX | 136 | 1496 | 3 (0)| 00:00:01 |
| 8 | INDEX FAST FULL SCAN| T1_ID | 51177 | 249K| 27 (4)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"="$nso_col_1")
6 - filter("OWNER"='SYS')
7 - access("OWNER"='SCOTT')
SQL> select count(*) from t1 where object_id in (select object_id from t2 where owner='SYS' minus select object_id from test where owner='SCOTT');
已用时间: 00: 00: 00.03
执行计划
----------------------------------------------------------
Plan hash value: 182265946
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18 | 8 (25)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 18 | | |
| 2 | NESTED LOOPS | | 1 | 18 | 8 (25)| 00:00:01 |
| 3 | VIEW | VW_NSO_1 | 1 | 13 | 7 (29)| 00:00:01 |
| 4 | MINUS | | | | | |
| 5 | SORT UNIQUE | | 1 | 30 | | |
|* 6 | TABLE ACCESS FULL| T2 | 1 | 30 | 2 (0)| 00:00:01 |
| 7 | SORT UNIQUE | | 136 | 1496 | | |
|* 8 | INDEX RANGE SCAN | XXX | 136 | 1496 | 3 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | T1_ID | 1 | 5 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter("OWNER"='SYS')
8 - access("OWNER"='SCOTT')
9 - access("OBJECT_ID"="$nso_col_1")
SQL> select count(*) from t1 where object_id in (SELECT LEVEL FROM DUAL CONNECT BY LEVEL<100);
已用时间: 00: 00: 00.05
执行计划
----------------------------------------------------------
Plan hash value: 1672622903
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18 | 4 (25)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 18 | | |
| 2 | NESTED LOOPS | | 1 | 18 | 4 (25)| 00:00:01 |
| 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| | | | | |
| 6 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | T1_ID | 1 | 5 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter(LEVEL<100)
7 - access("OBJECT_ID"="$nso_col_1")
看到了吧，当子查询里面有 (max/min/avg,group by), rownum, UNION,UNION ALL,INTERSECT,MINUS,connect by ...start with ，
ORACLE会将子查询转换为视图VW_NSO_x，其实这些限制也是VIEW MERGE的限制，当一个视图中有上面的条件，ORACLE就无法对视图进行merge。
如果你不想CBO生成VW_NSO_1，你只需要在子查询里面加上hint NO_UNNEST 即可，这时候CBO就会走FILTER。
