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。