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
“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.
I’m Bobby Curtis and I’m just your normal average guy who has been working in the technology field for awhile (started when I was 18 with the US Army). The goal of this blog has changed a bit over the years. Initially, it was a general blog where I wrote thoughts down. Then it changed to focus on the Oracle Database, Oracle Enterprise Manager, and eventually Oracle GoldenGate.
If you want to follow me on a more timely manner, I can be followed on twitter at @dbasolved or on LinkedIn under “Bobby Curtis MBA”.