Oracle Scratchpad

October 29, 2006

Virtual Columns – 11g

Filed under: CBO,Infrastructure,Oracle,Tuning — Jonathan Lewis @ 8:00 pm GMT Oct 29,2006

In one of his blogs on Open World Mark Rittman lists some of the features that Oracle may (subject to the usual comments from Oracle that this is a loose statement of intention, not a promise) include in 11g.

One of the small but, to my mind, very useful features that Mark mentions is this:

  • Virtual columns, columns that are actually functions (“create table t1 (c1 number, c2 number, c3 as (c1+c2) virtual”), and similarly, virtual indexes that are based on functions

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 so, as I said in my book (Cost Based Oracle Fundamentals) Chapter 6, Page 133:

  • It would be nice if Oracle Corp added the option for unindexed virtual columns, so we could have the benefit of correct statistics for commonly used expressions without the need to create the index.

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.


  1. This would be a brilliant feature.

    Do you think it will support updating of those virtual columns? i.e. requiring a trigger to handle the value if such an update is logical – similar to the way we can create instead-of triggers on views which translate dml on the view to dml on the base tables…

    Comment by Jeff Kemp — October 30, 2006 @ 11:57 am GMT Oct 30,2006 | Reply

  2. Re: Jeff Kemp / Updating virtual columns:

    Given the need for general purpose code, I would be surprised if anyone tried to implement such a feature, at least not in the initial versions. The scope for inconsistency and confusion seems far too high a risk.

    Comment by Jonathan Lewis — October 30, 2006 @ 12:56 pm GMT Oct 30,2006 | Reply

  3. In many cases the resultant change on the non-virtual column would be indeterminate anyway. UPPER(), TRUNC(), SUBSTR() etc. all spring immediately to mind.

    Comment by David Aldridge — October 30, 2006 @ 3:08 pm GMT Oct 30,2006 | Reply

  4. jl, a bit late to pitch in …. but virtual columns would be very useful incases where applictaions have lot of lov kind of things, if we take the forms implementation of the lov, it would generate all the possibilites to make it a case-sensitive search, there a upper will be added on the column, in that cases, the virtual column with index would be much much better as the correct statistics would be given to the cost based optimizer.

    few more things, we cannot update/insert values into the virtual column as they are derived.

    but what would be the use of non-indexed virtual columns?????? what would the cost based optimizer do even if it finds the column to be selective?

    Comment by avanish — May 1, 2007 @ 6:17 am BST May 1,2007 | Reply

  5. […] estimate resonably accurately how many employess have salary + nvl(commission,0) > 100000. But (until 11g, perhaps) you can’t create virtual columns unless you index them using the feature that Oracle […]

    Pingback by Virtual columns revisited « Oracle Scratchpad — April 15, 2009 @ 12:24 pm BST Apr 15,2009 | Reply

RSS feed for comments on this post.

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: