Explain VIEW

A brief note on reading execution plans.

------------------------------------------------------------
| Id  | Operation                     | Name    | Time     |
------------------------------------------------------------
|   0 | SELECT STATEMENT              |         | 00:00:17 |
|*  1 |  HASH JOIN                    |         | 00:00:17 |
|   2 |   VIEW                        | VW_SQ_1 | 00:00:01 |
|   3 |    HASH GROUP BY              |         | 00:00:01 |
|   4 |     TABLE ACCESS FULL         | EMP     | 00:00:01 |
|   5 |   VIEW                        |         | 00:00:17 |
|   6 |    TABLE ACCESS BY INDEX ROWID| EMP     | 00:00:17 |
|   7 |     INDEX FULL SCAN           | E_D     | 00:00:01 |
------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("DEPT_NO"="OUTER"."DEPT_NO")
       filter("OUTER"."SAL">"VW_COL_1")

Where you see the VIEW operator this is Oracle telling you that it has had to “suspend” operations and produce an intermediate result set by running the “sub-plan” under the VIEW.

You’re likely to see this operator if your query includes a non-mergeable view (perhaps because of a no_merge hint), or if Oracle has transformed your query in some way that has produced a non-mergeable view.

In the example above, one of the view operators (the one at line 2) refers to an object named “VW_SQ_1”. This is an internal view name that gives me a clue that Oracle has unnested an “existence” subquery. Its a convenient fact that many of the names generated by Oracle start with “VW_”, e.g. vw_nso_1, vw_nsq_1, vw_wif_1, vw_gbc_5, though there are some, inevitably, that don’t follow this convention, e.g. index$_join$_001

The other view operator (line 5) has appeared because I had an inline view with a no_merge hint in the main query.

Although the view operator requires an intermediate result set to be built, it’s important to remember that it need not create the entire result set before passing rows up to its parent (which is why I put quote marks around the word “suspend” earlier on in the article – the presence of the view operator  may have no visible blocking effect on the execution).

Looking at the example – the hash group by at line 3 has to complete before any rows can be passed up to the view operator and on to build the hash table in line 1, so there is a blocking effect there. By comparison, in line 5 the view operator can retrieve rows from the emp table in line 6 one at a time by virtue of the index full scan in line 7 and pass them on up to line 1 to probe the hash table – there is no blocking effect associated with the view.

In fact, when you look closely at what’s going on, you can see that the while the view represents the need to recognise an intermediate result set, the need for a complete data set isn’t dictated by the view operator, it is a function of the first child of the view.