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.

Please follow and like: