Virtual Columns

Starting to look into Oracle 11gR2 functionality today and found a feature that I like a bit. This feature is the “Virtual Column” feature. It basically, lets you define a column from derived data within the database. This can be done on both DDL and DML statements.

Virtual Columns can be used pretty much anywhere where a normal column can be used. These columns can be queried, indexed and statistics gathered on them. Like with anything new there are a few restrictions on what they can do.

Restrictions are as follow:

  • Cannot write to a virtual column
  • No support for IOT, external, object, cluster, or temp tables
  • No support for Oracle-supplied datatypes, user-defined types, LOBs, and LONG RAWs.

Now for a few examples

DDL Example (New table)

For this example I will be using the EMP table in the SCOTT schema. We will make a copy of the table (selected colums) and add a virtual column for 25% increase in salary.

SELECT* FROM SCOTT.EMP;

DROP TABLE SCOTT.EMP2;

CREATE TABLE SCOTT.EMP2

(“ENAME” VARCHAR2(10),

“SAL” NUMBER(7,2),

“25PCT_INCREASE” AS (SAL*.25)

);

 

SELECT* FROM SCOTT.EMP2;
//returns 0 records

 

The default key word is used to insert what the calculation would be into the virtual column.

 

INSERT INTO SCOTT.EMP2 VALUES(‘jsmith’,60000,DEFAULT);

INSERT INTO SCOTT.EMP2 VALUES(‘bconrad’,75000,DEFAULT);

INSERT INTO SCOTT.EMP2 VALUES(‘padam’,35000,DEFAULT);

 

SELECT* FROM SCOTT.EMP2;
//returns 3 records with virtual column calculated

 

ENAME SAL 25PCT_INCREASE

———- ———————- ———————-

jsmith 60000 15000

bconrad 75000 18750

padam 35000 8750

DDL Example (alter table)

For this example we will just add a virtual column to the EMP table in the SCOTT schema.

SELECT * FROM SCOTT.EMP; //returns normal table information

ALTER TABLE SCOTT.EMP

ADD(“15PCT_INCREASE” AS (SAL*.15));

 

SELECT * FROM SCOTT.EMP //returns a subset of data with virtual column

WHERE ROWNUM < 5;

 

ENAME JOB SAL 15PCT_INCREASE

———- ——— ———————- ———————-

SMITH CLERK 800 120

ALLEN SALESMAN 1600 240

WARD SALESMAN 1250 187.5

JONES MANAGER 2975 446.25

 

 

To drop a virtual column is just the same as dropping any other column of a table.

 

If you wanted to see what columns have a default value assigned; the view DBA_TAB_COLUMNS has a DATA_DEFAULT column which will give you the information.

 


 

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

%d bloggers like this: