11G

Virtual Column

User Rating:  / 0
PoorBest 
Parent Category: Articles
Created on Sunday, 21 March 2010 21:24
Last Updated on Monday, 12 March 2012 13:54
Published on Sunday, 21 March 2010 21:24
Written by Guy Lambregts
Hits: 3330

Virtual Columns

 

Virtual Columns are new in 11G. The columns aren' t physically stored in the table in stead the they are function of some other column(s) of the table row.

Here is an example


create table TABLE_TV2 (
TV2_ID NUMBER(18) not null,
TV2_name  varchar2(100) not null,
TV2_FIXED_SALARY number(10,2) not null,
TV2_VARIABLE_SALARY number(10,2) not null,
TV2_TOTAL_SALARY number(10,2) GENERATED ALWAYS AS
( TV2_FIXED_SALARY + TV2_VARIABLE_SALARY ) VIRTUAL
);



alter table TABLE_TV2 add constraint PK_TV2 primary key (TV2_ID);

create sequence SEQ_TABLE_TV2 cache 1000;

create index TV1_IDX2 on TABLE_TV2 (TV2_TOTAL_SALARY) initrans 4;

alter table TABLE_TV2 add constraint CK_TV2_TOTAL_SALARY check ( TV2_TOTAL_SALARY > 0 );


We can index virtual columns, create constraints on it, they can be used as a See Database Design : Tables : Virtual Column Partitioning

Virtual columns can' t be used in IOT' s, clustered tables, external tables nor in temporary tables.