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_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 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:
-
Join Factorization — currently needs password (Croatian Oracle User Group), see comment #1
Group by Placement
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 UTC Feb 27,2011 |
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 UTC Feb 28,2011 |
If anyone is interested, just simply registering at Jože Senegačnik , the is available to download.
Comment by Óscar de la Torre — February 28, 2011 @ 1:50 pm UTC Feb 28,2011 |
Comment by Timur Akhmadeev — February 27, 2011 @ 7:54 pm UTC Feb 27,2011 |
Timur,
Thanks for that.
I’ll copy the extra ones up and see how the labelling contributions go.
Comment by Jonathan Lewis — February 28, 2011 @ 9:30 am UTC Feb 28,2011 |
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 UTC Feb 27,2011 |
Tanel,
Thanks – now added to list.
Comment by Jonathan Lewis — February 28, 2011 @ 9:37 am UTC Feb 28,2011 |
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 UTC Feb 28,2011 |
[...] 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 UTC Feb 28,2011 |
[...] Further information on those Internal View names: Jonathan Lewis – Internal Views [...]
Pingback by JF – Join Factorization « OraStory — June 7, 2011 @ 3:31 pm UTC Jun 7,2011 |
When Table expansion transformation happen?
Comment by Edibert — July 20, 2012 @ 10:26 am UTC Jul 20,2012 |
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 UTC Jul 20,2012 |