You’ll often see comments that views are not copies of the data, they are merely the stored text of a query that defines.
This is by and large true, but don’t forget, this is not the same as saying we simply take whatever text you give us, and store it.
Let’s take a look at a simple example proving that this is not the case.
SQL> create table t as select * from all_objects;
Table created.
SQL>
SQL> create or replace
2 view V as select * from T;
View created.
SQL>
SQL> select text_vc from user_views
2 where view_name = 'V';
TEXT_VC
------------------------------------------------------------
select "OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","D
ATA_OBJECT_ID","OBJECT_TYPE","CREATED","LAST_DDL_TIME","TIME
STAMP","STATUS","TEMPORARY","GENERATED","SECONDARY","NAMESPA
CE","EDITION_NAME","SHARING","EDITIONABLE","ORACLE_MAINTAINE
D" from T
Notice that “*” has been expanded out to the full list of columns.
Sidebar: Notice also the nice column TEXT_VC which is the varchar2 equivalent of the older style LONG column TEXT. You’ll get that on 12c.
Interestingly, aliases will be preserved in the view definition if provided
SQL> create or replace
2 view V as select t.* from T;
View created.
SQL>
SQL> select text_vc from user_views
2 where view_name = 'V';
TEXT_VC
------------------------------------------------------------
select t."OWNER",t."OBJECT_NAME",t."SUBOBJECT_NAME",t."OBJEC
T_ID",t."DATA_OBJECT_ID",t."OBJECT_TYPE",t."CREATED",t."LAST
_DDL_TIME",t."TIMESTAMP",t."STATUS",t."TEMPORARY",t."GENERAT
ED",t."SECONDARY",t."NAMESPACE",t."EDITION_NAME",t."SHARING"
,t."EDITIONABLE",t."ORACLE_MAINTAINED" from T
One important takeaway from this, is that since the “*” is not preserved in the view definition, if you add or drop columns to the base table, the view needs to be recreated to correctly reflect that change.
Learning is not a spectator sport 
Leave a reply to Matthias Rogel Cancel reply