Oracle Scratchpad

February 27, 2011

Internal Views

Filed under: CBO,Execution plans — Jonathan Lewis @ 6:10 pm GMT Feb 27,2011

When Oracle transforms a query it will sometimes report an internal view name in the execution plan – and I have seen people asking for help occasionally because they’ve tried to find the view name in dba_views and it hasn’t existed. So, just for reference, here’s a short list of the view names that might appear during query transformation but won’t exist in your system.

With each name I’ve put a brief comment of why they might appear:

index$_join$_#  Index hash join
vw_nso_#		Unnesting "IN" subquery
vw_nsq_#		Haven't seen one recently, but I think it's a variation on "IN" subqueries
vw_sq_#			Unnesting - possibly specific to correlated subqueries
vw_gbc_#		group by placement
vw_gbf_#		group by placement
vw_gbr_#		group by placement
vw_wif_#		Analytic functions (especially those generated by "remove aggregate subquery")
vm_nwvw_#		Note "VM" rather than "VW" - related to complex view merging with distinct
vw_foj_#		Full Outer Join
vw_jf_????		Join Factorization
vw_ore_#		(New) OR Expansion
-- From Timur Akhmadeev (comment #2)
VW_COL_%d          # Naming convention (somtimes) for columns used in generated views
VW_DAT_%08X        # distinct aggregate transform?
VW_DIS_%08X        # distinct (similar to group by placement)?
VW_DTP_%08X        # distinct placement (?) -- jpl
VW_ST_%08X         # related to Star Transformation -- Kaley Crum, comment #12
-- From Tanel Poder (comment #3)
VW_TE_#         Table expansion transformation
VW_DAG_#        Distinct aggregatation transform
-- From Mohammed Houri (comment #11)
VW_LAT_#        Lateral View
VW_DCL_#        Decorrelated Lateral View

Note: I’ve used ‘#’ to represent the possible appearance of a number, the items supplied by Timur Akhmedeev use the C “printf” conventions.

Originally I called for suggestions of the ones I’d failed to report – but the list is now much closer to completion. If you’d like to contribute any further names names, or fill in some of the missing causes I’ll add them to the list.

For reference notes for a couple of these cases:

Join Factorization

Group by Placement


  1. [oracle@localhost bin]$ strings oracle | grep -e '^VW_.*' | sort
    VW_DAT_%08X        # distinct aggregate transform?
    VW_DIS_%08X        # distinct (similar to group by placement)?

    Comment by Timur Akhmadeev — February 27, 2011 @ 7:54 pm GMT Feb 27,2011 | Reply

  2. There are a few more that I’ve noticed (and not shown in Timur’s list as they’re printed using the VW_%s_%d format string.

    1) The distinct aggregate transform view can shown as VW_DAG_n (I have not seen DAT used myself)
    2) Table expansion transformation is shown as VW_TE_n

    Comment by Tanel Poder — February 27, 2011 @ 9:09 pm GMT Feb 27,2011 | Reply

  3. […] a follow-up to a recent post on different names Oracle can use for the intermediate views, here is a quick example of the […]

    Pingback by Distinct placement « Timur Akhmadeev's blog — February 28, 2011 @ 7:01 pm GMT Feb 28,2011 | Reply

  4. […] Further information on those Internal View names: Jonathan Lewis – Internal Views […]

    Pingback by JF – Join Factorization « OraStory — June 7, 2011 @ 3:31 pm BST Jun 7,2011 | Reply

  5. When Table expansion transformation happen?

    Comment by Edibert — July 20, 2012 @ 10:26 am BST Jul 20,2012 | Reply

    • Edibert,

      You can see this in some execution plans that access a partitioned table through a local index when some of the index partitions have been marked unusable.
      Oracle is able to generate one plan for the partitions where the index partitions are usable and another where the index partitions are unusable.

      Comment by Jonathan Lewis — July 20, 2012 @ 11:08 am BST Jul 20,2012 | Reply

  6. Another great source of detailed information about cost based query transformations is the paper from Riyaj Shamsudeen.

    Click to access riyaj_cost_based_query_transformation_doc.pdf


    Comment by Stefan Koehler — June 30, 2013 @ 5:22 pm BST Jun 30,2013 | Reply

  7. […] После 100500-й итерации настройки я обратил внимание, что в проблемном плане выполнения я ни разу не встретил объекта с названием VW_SQ_N, в то время как в желаемом плане их было несколько. Такого рода имена даются блокам запроса после выполнения стоимостных преобразований, дискуссия в блоге jonathan lewis […]

    Pingback by Материализованные представления и subquery unnesting | Yandex DB blogs and notes — January 28, 2014 @ 8:16 pm GMT Jan 28,2014 | Reply

  8. […] It’s still easy to see our subquery here in the VIEW operator which references an object called VW_SQ_1 (one of several transformation-specific names that Oracle uses for views generated internally). The VW_ prefix appears very commonly, and the SQ bit appears fairly commonly for subquery unnesting. (For a list of many other generated view names see […]

    Pingback by Basics of the Cost Based Optimizer – Part 4 – All Things Oracle — July 9, 2015 @ 9:36 am BST Jul 9,2015 | Reply

  9. Jonathan,

    It is probably worth including the De-correlated Lateral view (VW_DCL#) in this list of internal views for the sake of completeness

    Best regards
    Mohamed Houri

    Comment by hourim — April 1, 2017 @ 7:46 pm BST Apr 1,2017 | Reply

  10. […] changing the view with “distinct” into a (right) semi-join between t2 and t1 with the internal view name of VM_NWVW_1 – and the cardinality is […]

    Pingback by opt_estimate 4 | Oracle Scratchpad — July 1, 2019 @ 1:18 pm BST Jul 1,2019 | Reply

  11. […] the optimizer has “unnested” the subquery to create an inline view (which it has named VW_NSO_1) and then used a simple join to get the final result. That’s an interesting observation, and […]

    Pingback by dense_rank | Oracle Scratchpad — March 12, 2020 @ 6:42 pm GMT Mar 12,2020 | Reply

  12. I believe VW_ST_%08X is related to star transformation. I’m basing this information off of the example laid out here:

    Comment by Kaley Crum — November 5, 2020 @ 7:27 pm GMT Nov 5,2020 | Reply

    • Kaley,

      Thanks for the addition. I’ve added it to the list.

      Interestingly I’ve just re-run Timur’s check against the binary for Oracle 19c and a lot of the VW_ labels that used to show up don’t seem to any more, possibly having been replaced by generic calls that use one of three format strings that do appear:


      Jonathan Lewis

      Comment by Jonathan Lewis — November 7, 2020 @ 12:55 pm GMT Nov 7,2020 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: