Internal Views

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_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         # related to Star Transformation -- Kaley Crum, comment #12
VW_STJ_%08X
VW_TEX_%08X
--
-- 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