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.

 


 

Advertisements

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

Pardy DBA

ORA-00001: unique constraint (ORA.BLOG_TAGLINE_PK) violated

Martin Widlake's Yet Another Oracle Blog

Oracle performance, Oracle statistics and VLDBs

HeliFromFinland

Heli's thoughts on Database Designing, Oracle SQL Developer Data Modeler, User Groups etc.

Julian Dontcheff's Database Blog

The good DBA is one who learns from his mistakes, the best DBA is one who learns from other DBA's mistakes

Martins Blog

Trying to explain complex things in simple terms

The Data Warrior

Changing the world, one data model at a time. How can I help you?

Maaz Anjum's Blog

A life yet to be lived...

PeteWhoDidNotTweet.com

Stuff that interests me, if not you!

Uwe Hesse

about Database Technology

%d bloggers like this: