What is vw_nso_1 view?

Vw_nso_1 is created dynamically when we view the execution plan. VM is the agreed prefix/Suffix of the Oracle view. When working with foreigners, we often see the suffix of VW, it indicates the view, which is a habit.

The following is the view of a certain data warehouse in the daily necessities industry. Its suffix is 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

........................................ ............................. Omitting ....................................... ...............................

NSO can be understood as follows: Nested subquery optimizing converts in to join and not in to Anti join (if the column is not null)

When Will Oracle generate a view like vw_nso_1? See the following examples (based on Oracle10g ):

SQL> select count (*) from T1 where object_id not in (select max (object_id) from T2 group by owner); used time: 00: 00: 00.05 execution plan -------------------------------------------------------- plan hash value: 350791718 bytes | ID | operation | Name | rows | bytes | cost (% CPU) | time | period | 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 | identified 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); used time: 00: 00: 00.01 execution plan ------------------------------------------------------ plan hash value: 4201411252 bytes | ID | operation | Name | rows | bytes | cost (% CPU) | time | bytes | 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 | identified predicate information (identified by Operation ID): Limit 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); used time: 00: 00: 00.00 execution plan ---------------------------------------------------- plan hash value: 3285035028 bytes | ID | operation | Name | rows | bytes | cost (% CPU) | time | bytes | 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 | identified 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'); used time: 00: 00: 00.06 execution plan -------------------------------------------------------- plan hash value: 3457113806 bytes | ID | operation | Name | rows | bytes | cost (% CPU) | time | period | 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 | identified 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 '); used time: 00: 00: 00.03 execution plan -------------------------------------------------------- plan hash value: 182265946 bytes | ID | operation | Name | rows | bytes | cost (% CPU) | time | minute | 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 | identified 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); used time: 00: 00: 00.05 execution plan ------------------------------------------------------ plan hash value: 1672622903 bytes | ID | operation | Name | rows | bytes | cost (% CPU) | time | bytes | 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 | identified predicate information (identified by Operation ID ): ------------------------------------------------- 5-filter (level <100) 7-access ("object_id" = "$ nso_col_1 ")

See, when the subquery contains (max/MIN/AVG, group by), rownum, Union, Union all, intersect, minus, connect by... start,

Oracle converts a subquery to a view vw_nso_x. In fact, these restrictions are also restrictions on view merge. When a view has the preceding conditions, Oracle cannot perform merge on The View.

If you do not want CBO to generate vw_nso_1, you only need to add hint no_unnest to the subquery. At this time, CBO will use the filter.