Oracle Scratchpad

February 27, 2011

Internal Views

Filed under: CBO,Execution plans — Jonathan Lewis @ 6:10 pm BST 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 b placement
vw_wif_#		Analytic functions (especially those generated by "remove aggregate subquery")
vm_nwvw_#		Note "VM" rather than "VW" - possibly related to complex view merging with distinct
vw_foj_#		Full Outer Join
vw_jf_????		Join Factorization
-- 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         
-- From Tane Poder (comment #3)
VW_TE_#         Table expansion transformation
VW_DAG_#        Distinct aggregatation transform

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:


  1. Jonathan,
    the link to Joze Senegacnik’s pdf on Join Factorization seems to require special authorization – “Uskraćen pristup: You do not have permission to access this area.”

    Comment by Martin Preiss — February 27, 2011 @ 6:25 pm BST Feb 27,2011 | Reply

    • Martin,

      Thank you for the warning. When I downloaded the presentation at home after a simple google search there was no problem; but the file is on the web pages of the Slovenian Oracle User Group, and I guess the google cache in my home machine must have the id/password from the time I presented at their annual conference.

      I have seen a note on Julian Dyke’s blog that it might be available as one of the OOW 2010 presentations for people who have access to those downloads.

      Comment by Jonathan Lewis — February 28, 2011 @ 9:26 am BST Feb 28,2011 | Reply

  2. [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 BST Feb 27,2011 | Reply

  3. 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 BST Feb 27,2011 | Reply

  4. Hi Jonathan,
    Just one small correction. hroug is Croatian Oracle user group. Slovenian is sioug. :)
    Cheers. :)

    [Now fixed – JPL]

    Comment by Domagoj — February 28, 2011 @ 2:47 pm BST Feb 28,2011 | Reply

  5. […] 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 BST Feb 28,2011 | Reply

  6. […] 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

  7. 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

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


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

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

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

  10. […] 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

  11. 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

RSS feed for comments on this post. TrackBack URI

Leave a Reply

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 )

Google+ photo

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

Connecting to %s

Powered by