Views as “stored text”

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.

One thought on “Views as “stored text”

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