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_CDG_%08X
VW_COL_%d          # Naming convention (somtimes) for columns used in generated views
VW_DAT_%08X        # distinct aggregate transform?
VW_DIG_%08X
VW_DIS_%08X        # distinct (similar to group by placement)?
VW_DTP_%08X        # distinct placement (?) -- jpl         
VW_FTG_%08X
VW_LT_%08X
VW_RIG_%08X
VW_RT_%08X
VW_%s_%d
VW_SJE_%08X
VW_%s_%s
VW_ST_%08X
VW_STJ_%08X
VW_TEX_%08X
--
-- 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:

15 Comments »

  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_CDG_%08X
    VW_COL_%d
    VW_DAT_%08X        # distinct aggregate transform?
    VW_DIG_%08X
    VW_DIS_%08X        # distinct (similar to group by placement)?
    VW_DTP_%08X        
    VW_FOJ_%d
    VW_FTG_%08X
    VW_JF_%08X
    VW_LT_%08X
    VW_NSO_%d
    VW_RIG_%08X
    VW_RT_%08X
    VW_%s_%d
    VW_SJE_%08X
    VW_SQ_%08X
    VW_%s_%s
    VW_ST_%08X
    VW_STJ_%08X
    VW_TEX_%08X
    VW_WIF_%08X
    

    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.

    http://www.orainternals.com/wp-content/uploads/2011/12/riyaj_cost_based_query_transformation_doc.pdf

    Regards
    Stefan

    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


RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 4,014 other followers