Virtual Columns

A recent post on comp.databases.oracle.server asked:

I have read various articles on virtual columns? I still do not understand their advantages other than they save some disk space, one can put logic of virtual columns in a trigger which will save information in a real column. Real column will need some additional disk space, but it will save some cpu time when one is doing selects on “virtual” columns.

In my opinion it’s the optimizer statistics that are likely to be the most common benefit of virtual columns: If you write the query:

select * from emp where sal + nvl(comm,0) >= 10000

then the selectivity is 5% (under the standard rule for “range based predicate involving unknown values”), and it would be 1% (“column expression with equality”) for:

select * from emp where sal + nvl(comm,0) = 10000

In earlier versions of Oracle you could create a function based index on (sal + nvl(comm,0)) which would give you a hidden column definition and allow you to generate real statistics on the expression – but you have to allow the space for the supporting index when (possibly) all you really want is the set of column statistics. [but see footnote **1]

In 11g if you declare a virtual column, tot_remun say, as (sal + nvl(comm,0)) then you can have the statistics on tot_remun without using up any extra space (beyond the small number of entries in the data dictionary). [see footnote **2]

alter table emp
add (
        tot_remun generated always as ( sal + nvl(comm,0) ) virtual
)
;

As 11g gets taken up in more production systems, I expect to see sites solving a number of optimizer problems simply by creating virtual columns on expressions that appear frequently in important queries.

Footnotes:
**1: In fact, you can collect statistics on “virtual columns” pre-11g by creating function-based indexes in an unusable state. The method works, but it’s not really a good strategy.

**2: In fact, by making a call to dbms_stats.create_extended_stats()  you can collect stats on an expression such as (sal + nvl(comm,0)) without creating a virtual column But this will create exactly the same hidden virtual column that you would have had anyway, with the drawback (or possibly benefit, depending on your point of view) that you don’t have a user-friendly column name to reference in your code.

3: [November 2010] I see that Richard Foote has been uncovering some problems with virtual columns. As so often happens with new Oracle features combinations cause problems. In this case it’s materialized views with fast refresh that run into trouble when you start using virtual columns.

4: [April 2011] At Miracle Open World 2011, I discovered in a conversation with Morten Egan of MiracleAS another issue with virtual columns – exp (the old export mechanism) won’t export a table if it’s been defined with a virtual column. Of course, if you’re up to version 11g, you should be using datapump (expdp) rather than the older product, but it’s a trap that’s still going to catch someone out.

5: [June 2011] And Karen Morton has found another nasty problem if you’re on 11.2.0.2