Oracle 11g – Virtual Columns – to publish derived column values without storing them. And more…

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.

Resources

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