Oracle 11g introduces Virtual Columns. “What do we need Virtual Columns for, aren’t real ones more useful?” you might wonder. And what are they to begin with?
A virtual column is a construct that looks like a column when you describe or query a table, when you inspect meta-data, when you define indexes and constraints (except for primary key constraints). If it walks and talks like a Column, doesn’t that mean that it is a column? Well, normal columns actually store values. Virtual Columns do not! All they store is their definition. And when indexed, they have values – their expression evaluated – stored in the index. Note: virtual columns can not be set in insert and update statement – no surprise there!
If you are familiar with the concept of Function Based Indexes, you may have seen something that is very close to the Virtual Column (see https://technology.amis.nl/blog/?p=1324 and especially the section One more thing…) – Virtual Columns seem to be little more than the exposure of the the system generated column for a Function Based Index-es.
Virtual Columns are useful in those situations where values derived from the real column values are frequently required, yet would be a denormalization in the data design when implemented as real column. In the EMP table, this applies for example to INCOME (the sum of SAL and COMM), HIREYEAR (the year component of the HIREDATE). Using Virtual Columns will reduce the need for Views to provide derived column values.
Jonathan Lewis (http://jonathanlewis.wordpress.com/2006/10/29/virtual-columns-11g/ ) suggests another good use for Virtual Columns: so we could have the benefit of correct statistics for commonly used expressions without the need to create the index. [ One of the commonest root causes of errors in the calculations made by the Cost Based Optimizer is the use of incorrect data types (e.g. dates stored as numbers). In the past I have occasionally been able to work around such problems by creating “function-based indexes” – which I prefer to call “indexes involving virtual columns”.
But if you don’t really need (or want) to create new indexes it’s a nasty maintenance overhead simply to get improved statistics. With a little luck the 11g implementation will allow us to add virtual columns to existing 3rd party tables in a way that doesn’t affect any bad code in the application but allows predicates involving functions to be rewritten by the optimizer as predicates against virtual columns.]
You can use all columns (but not virtual columns) in the table on which the Virtual Column is defined. You can include function calls in the Virtual Column definition, either standard SQL functions or deterministic user defined functions. You can build an index – and therefore also a Unique Constraint – on Virtual Columns.
The easiest example around:
alter table emp ADD (income AS (sal + nvl(comm,0))) /
Here we add a new column INCOME to the EMP table, specifying it as a VIRTUAL COLUMN through the use of AS (column expression).
This column is henceforth available in USER_TAB_COLUMNS, DESC EMP and all queries against EMP.
SQL> desc emp Name Type ------------------------------- EMPNO NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) INCOME NUMBER SQL> select empno 2 , ename 3 , sal 4 , comm 5 , income 6 from emp 7 / EMPNO ENAME SAL COMM INCOME ---------- ---------- ---------- ---------- ---------- 7369 SMITH 800 800 7499 ALLEN 1600 300 1900 7521 WARD 1250 500 1750 7566 JONES 2975 2975 7654 MARTIN 1250 1400 2650 7698 BLAKE 2850 2850 7782 CLARK 2450 2450 7839 KING 5000 5000 7844 TURNER 1500 0 1500 7876 ADAMS 1100 1100 7900 JAMES 950 950 7934 MILLER 1300 1300 7788 SCOTT 3750 3750 7902 FORD 3000 3000 14 rows selected.
Updating the salary column automatically ‘refreshes’ the Virtual Column. Well, querying the Virtual Column after the update renders a freshly calculated result:
SQL> update emp 2 set sal = sal * 1.25 3 where ename = 'MARTIN' 4 / 1 row updated. SQL> SQL> select empno 2 , ename 3 , sal 4 , comm 5 , income 6 from emp 7 where ename = 'MARTIN' 8 / EMPNO ENAME SAL COMM INCOME ---------- ---------- ---------- ---------- ---------- 7654 MARTIN 1562.5 1400 2962.5 1 row selected.
Virtual Columns and Constraints
Perhaps somewhat surprising, Virtual Columns can be used in Check Constraints (Foreign Keys – like Unique and Primary Keys – are also supported.). See here an example:
alter table emp add constraint emp_income_ck check ( income < 8000) / update emp set sal = sal * 2 / update emp * ERROR at line 1: ORA-02290: check constraint (SCOTT.EMP_INCOME_CK) violated
This Check Constraint is enforced whenever INCOME is changed – which is never. Or at least, never directly. However, indirectly is INCOME changed whenever any of the columns in the INCOME Virtual Column Expression changes – SAL and COMM – and for any of those columns, changes will trigger validation of the Check Constraint.
Being able to use a Virtual Column in a Check Constraint means that indirectly we can use user defined functions in Check Constraints! Something check constraints themselves do not allow.
alter table emp ADD (sal_ck AS (income_check(deptno,sal))) / alter table emp add constraint emp_sal_ck check ( sal_ck = 'Y') / prompt this check constraint is almost: income_check(sal, deptno) ='Y', written in a round about way
However, this function is somewhat restricted: the function must be deterministic and it cannot select from table EMP – as it will run into the Mutating Table problem. I thought to resolve that issue by turning the constraint into a deferred one – one that is enforced only at commit time, when the statement is complete and the table is no longer mutating. However, for some reason that does not seem to work. What does work is adding a PRAGMA AUTONOMOUS_TRANSACTION to the function definition. Unfortunately, that means that function cannot see the changes made in the transaction that triggers the check constraint.
rem even deferred check constraints on virtual columns will immediately call the user defined function alter table emp add constraint emp_sal_ck check ( sal_ck = 'Y') INITIALLY DEFERRED DEFERRABLE / update emp set sal = 7000 where sal = 5000 / SQL> update emp set sal = sal+1 2 / update emp set sal = sal+1 * ERROR at line 1: ORA-04091: table SCOTT.EMP is mutating, trigger/function may not see it ORA-06512: at "SCOTT.INCOME_CHECK", line 10
Foreign Key on Virtual Column
Suppose we have a reorganization of the DEPT table: Departments 10-25 are in one table – DEPT_US – and other Departments are in table DEPT_ROTW. This means that we can no longer define a Foreign Key on DEPTNO. However, we can create virtual columns DEPTNO_US and DEPTNO_ROTW like this: case when deptno<= 30 then deptno end and case when deptno> 30 then deptno end and then define foreign keys to DEPT_US and DEPTNO_ROTW on those two virtual columns. I know, it is an odd scenario, but one easily pictured and therefore helpful to illustrate the concept.
create table dept_us as select * from dept where deptno<25 / alter table dept_us add constraint dept_us_pk primary key (deptno) / create table dept_rotw as select * from dept where deptno>25 / alter table dept_rotw add constraint dept_rotw_pk primary key (deptno) / alter table emp add (deptno_us as (case when deptno<25 then deptno end)) / alter table emp add (deptno_rotw as (case when deptno>25 then deptno end)) / alter table emp add constraint emp_deptno_us_fk foreign key (deptno_us) references dept_us (deptno) / alter table emp add constraint emp_deptno_rotw_fk foreign key (deptno_rotw) references dept_rotw (deptno) / update emp set deptno = 40 where deptno = 20 / delete from dept_rotw where deptno = 40 /
Another far fetched example is the implementation of a business rule that states: we do not want to hire employees in years that we not already have people hired in. One wonders how you can hire people in the past, but it is just an example of what foreign keys on virtual columns can be used for.
First we create a Materialized View with the values of years in which we have hired employees. Then we define a Unique Key – referencable by a foreign key – on this Materialized View. Then we define a Virtual Column HIREYEAR on Table EMP. Finally we create a Foreign Key on EMP (HIREYEAR) that references the Hireyear in the Materialized View. We can no longer insert records into EMP with a HIREDATE outside the Hireyears available in the Materialized View!
create materialized view emp_hireyears as select distinct extract (year from hiredate) hireyear from emp / alter materialized view emp_hireyears add constraint emp_hy_uk1 unique (hireyear) / alter table emp add hireyear as (extract (year from hiredate)) / alter table emp add constraint emp_hireyear_chk foreign key (hireyear) references emp_hireyears (hireyear) / update emp set hiredate = hiredate + 365 where deptno = 10 / update emp * ERROR at line 1: ORA-02291: integrity constraint (SCOTT.EMP_HIREYEAR_CHK) violated - parent key not found insert into emp (empno, ename, deptno, hiredate) values (1010, 'ALEX', 10, sysdate) / insert into emp * ERROR at line 1: ORA-02291: integrity constraint (SCOTT.EMP_HIREYEAR_CHK) violated - parent key not found
Not very useful? Well, it may just spark your own imagination a little.
In a subsequent blog article, we will discuss how Virtual Columns can be used for various Dynamic Business Rule implementations.
Creating Virtual Columns as part of the Create Table statement, in the SQL Reference Guide: http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_7002.htm#SQLRF01402
Examples for Alter Table statement in SQL Reference Guide: http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_3001.htm#BABIDDJJ .
Relevant Error Messages: http://download.oracle.com/docs/cd/B28359_01/server.111/b28278/e53000.htm#sthref12629
2 thoughts on “Oracle 11g – Virtual Columns – to publish derived column values without storing them. And more…”
Arnoud, unfortunately the column expression may only refer to columns in the table on which the Virtual Column is defined.
However, you can use a user defined function in the Virtual Column expression; that function could perform a query from another table! By doing so, you violate the DETERMINISTIC character of the function – on your head be the consequences – and if you do not use the PRAGMA AUTONOMOUS_TRANSACTION you can run into Mutating Table problems, but otherwise it could work that way.
best regards, Lucas
This is really great stuff!
One question: Is is also possible to define a virtual column to a table based on information stored in another table (e.g. add a virtual column address to emp)
Comments are closed.