Oracle DBA – A lifelong learning experience

CTAS when using a virtual column in 11g

Posted by John Hallas on January 16, 2009

This blog is about the new 11g functionality virtual column and highlights a gotcha when a CTAS is used against such a table.

drop table vctest;

create table vctest (team varchar(12),played number,points number,

avg_points number (2,1) GENERATED ALWAYS as (points/played ));

insert into vctest (team,played,points) values (’Chelsea’,21, 42);

insert into vctest (team,played,points) values (’Liverpool’,21, 46);

insert into vctest (team,played,points) values (’Man Utd’,20,44 );

insert into vctest (team,played,points) values (’Arsenal’,21,38);

insert into vctest (team,played,points) values (’Aston Villa’,21,41);

select * from vctest order by 2 desc;

TEAM PLAYED POINTS AVG_POINTS
Chelsea 21 42 2
Liverpool 21 46 2.2
Aston Villa 21 41 2
Arsenal 21 38 1.8
Man Utd 20 44 2.2

create table vc_copy as select * from vctest;

select * from vc_copy order by 3 desc;

TEAM PLAYED POINTS AVG_POINTS
Chelsea 21 42 2
Liverpool 21 46 2.2
Aston Villa 21 41 2
Arsenal 21 38 1.8
Man Utd 20 44 2.2

The tables look identical but we test by adding the same row to both tables

Insert into vctest (team,played,points) values (’Everton’,21, 35);

Insert into vc_copy (team,played,points) values (’Everton’,21, 35);

commit;

vtest TEAM PLAYED POINTS AVG_POINTS
Everton 21 35 1.7

vc_copy TEAM PLAYED POINTS AVG_POINTS
Everton 21  35

Notice that the avg_points column is not completed for Everton as the table has been copied with the data values and not the statement that generates the values.

One workaround would be to copy table as before, drop the avg_points column and then create it as a virtual column again

If it is a large table then it is best to mark the column as unused and drop later

alter table vc_copy set unused (avg_points);

desc vc_copy;

Name Null Type
TEAM  VARCHAR2(12)
PLAYED NUMBER 
POINTS NUMBER 
3 rows selected

alter table vc_copy add avg_points number (2,1) GENERATED ALWAYS as (points/played );

desc vc_copy;

Name Null Type
TEAM  VARCHAR2(12)
PLAYED NUMBER 
POINTS NUMBER 
AVG_POINTS NUMBER(2,1) 
4 rows selected

select * from vc_copy order by 3 desc;

TEAM PLAYED POINTS AVG_POINTS
Liverpool 21 46 2.2
Man Utd 20 44 2.2
Chelsea 21 42 2
Aston Villa 21 41 2
Arsenal 21 38 1.8
Everton 21 35 1.7
6 rows selected

Finally, another option to create the table is to use dbms_metadata to grab the DDL to create the copy table as a select *. Edit the DDL to remove and then create the table, later adding the avg_points column as a virtual column

select DBMS_METADATA.GET_DDL(’TABLE’,’VCTEST’) from dual;

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

 
%d bloggers like this: