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.
Current Oracle Certs
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”.