Oracle Scratchpad

April 16, 2009

Virtual Columns

Filed under: Oracle,Performance,Statistics,Tuning — Jonathan Lewis @ 7:25 pm BST Apr 16,2009

A recent post on 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.

**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


  1. In addition to the extended statistics, there is the opportunity to partition by it, the opportunity to create foreign keys on them – think about that legacy system with the so called ‘intelligent key’ where the part number is the substr(primary_key,5,10). You can put a foreign key on it.

    Ease of use as well – add the virtual column and you can treat it like any other column – index it, constrain it, select it, gather statistics on it, partition by it… Most everything except.. alter it’s datatype :)

    Comment by Thomas Kyte — April 16, 2009 @ 7:53 pm BST Apr 16,2009 | Reply

  2. Tom,

    The foreign key constraint is a very nice idea – I remember seeing you describe it on AskTom some time ago.

    As for “changing data type” – no, not quite – but you can certainly create a virtual column of the form to_date(char_col,’yyyymmdd’) to deal with some of the problems of writing reports against “database independent” applications.

    Comment by Jonathan Lewis — April 16, 2009 @ 9:53 pm BST Apr 16,2009 | Reply

  3. Gents,

    thank you for sharing your time & knowledge !!

    Very simple and explanatory.

    Kind regards,

    Comment by Danyc — April 17, 2009 @ 8:17 pm BST Apr 17,2009 | Reply

  4. […] to the columns) has been given the fixed “unknown range” selectivity of 5%. (In this case, the virtual column technology introduced in Oracle 11g would have helped correct the cardinality error.) But where […]

    Pingback by Predicate Problems « Oracle Scratchpad — February 20, 2010 @ 6:53 pm GMT Feb 20,2010 | Reply

  5. […] you could ask to fill in further details here. For example, if you created a “genuine” virtual column in 11g using one of my “unusable” decode() expressions, and then indexed the virtual […]

    Pingback by FBI oddities « Oracle Scratchpad — December 16, 2010 @ 6:18 pm GMT Dec 16,2010 | Reply

  6. I’ve just picked up an interesting little detail about virtual columns at the MOW 2011 conference. exp doesn’t like them, and won’t export a table with a virtual column. (Still, for 11g you should be using expdp anyway.)

    Comment by Jonathan Lewis — April 18, 2011 @ 11:54 am BST Apr 18,2011 | Reply

  7. great article for oracle 11g virtual column support and its discrepancies

    Comment by lkafle — July 1, 2011 @ 4:53 am BST Jul 1,2011 | Reply

  8. There is another problem with virtual column when used in a DML error logging table. I have blogged about it here
    Best regards

    Comment by Mohamed Houri — September 7, 2013 @ 6:12 pm BST Sep 7,2013 | Reply

    • Mohamed,

      Thanks for the comment – it’s always useful to build a collection relevant details.
      If anyone wants to recreate your test on 12.1 they can always report the results here.

      Comment by Jonathan Lewis — September 8, 2013 @ 11:54 am BST Sep 8,2013 | Reply

  9. Jonathan,

    The problem is not reproducible in 12.1. even in the presence of an index on the virtual column

    SQL> select * from v$version;
    BANNER                                                                              CON_ID
    -------------------------------------------------------------------------------- ----------
    Oracle Database 12c Enterprise Edition Release - 64bit Production              0
    PL/SQL Release - Production                                                    0
    CORE	Production                                                            0
    TNS for 64-bit Windows: Version - Production                                   0
    NLSRTL Version - Production                                                    0
    SQL> describe err$_t1
     Name                            Null?    Type
     ------------------------------- -------- ----------------------------
        1      ORA_ERR_NUMBER$                NUMBER
        2      ORA_ERR_MESG$                  VARCHAR2(2000)
        3      ORA_ERR_ROWID$                 ROWID
        4      ORA_ERR_OPTYP$                 VARCHAR2(2)
        5      ORA_ERR_TAG$                   VARCHAR2(2000)
        6      N1                             VARCHAR2(4000)
        7      DAT1                           VARCHAR2(4000)
        8      VIRT_N1                        VARCHAR2(4000)
    SQL> insert into t1
      2      (n1
      3      ,dat1)
      4  select
      5     t2.n1
      6     ,systimestamp
      7  from t2
      8  log errors into ERR$_t1 reject limit unlimited;
    3 rows created.

    Comment by hourim — September 9, 2013 @ 7:45 am BST Sep 9,2013 | Reply

  10. Hi, exporting schemas with virtual columns has been an issue we we’ve been dealing with a lot; it doesn’t happen with every virtual column and it doesn’t happen on the export: the issue seems to do with the sequence of actions that take place during the impdp operation, and ONLY for virtual columns that are using user defined functions in their definition. The problem is impdp puts the data structure (i.e. table definition) in place, before it imports the functions; so the object (i.e. function) is not defined yet, hence the failure of the table import.

    – get rid of the virtual columns before export, and add them afterwards.
    – do the import multiple times: once for creating the user and the functions/procs/packages, and another time for tables and …
    – do the import twice regardless. The second import will not overwrite the existing data and will use the object definitions in place, but I suspect that can/will endanger the data integrity.

    I’d be grateful if you know of any method that can influence the sequence of objects that are being imported (as opposed to running impdp multiple times, once for each object type).


    Comment by Babak Tourani — November 20, 2013 @ 10:06 am GMT Nov 20,2013 | Reply

    • Sorry, forgot this:

      The issue with user defined functions seems to be caused by incorrect/inappropriate references to the owner schema of the function and appears when you remap the schema; so when you export the schema what’s available in the “data_default” column of USER_TAB_COLUMNS is included in the dump, which refers to a function as “original_schema_name.function;” but when it comes to importing (for example) “table” to “remapped_schema”, obviously defining the virtual column using the function “original_schema_name.function” is not possible!

      There are always work-arounds; what’s missing is a general approach.


      Comment by Babak Tourani — November 21, 2013 @ 11:10 am GMT Nov 21,2013 | Reply

      • Babak,
        I fear that there is no general approach, since there are still some bugs with virtual columns.
        For example, you can easily create a table with a virtual column which metadata cannot be replayed ! Which means this table cannot be imported at all !

        See ( also reproduced on 12.1 )

        create procedure replay_table_metadata( vtabname in varchar2 ) is
        sqlm clob;
           sqlm := dbms_metadata.get_ddl( 'TABLE', vtabname );
           execute immediate 'drop table ' || vtabname;
           dbms_output.put_line( sqlm || ' ... ');
           execute immediate sqlm;
        end replay_table_metadata;
        sokrates@11.2 > create table t( i int );
        Table created.
        sokrates@11.2 > exec replay_table_metadata ( 'T' )
           (    "I" NUMBER(*,0)
          TABLESPACE "USERS"  ...
        PL/SQL procedure successfully completed.

        Now, we create a table for which replay_table_metadata fails, so the table can be exported but not imported again:

        create package p is
           function func return int deterministic;
        end p;
        drop table t;
        create table t( i int , fun as ( p.func() ) );
        alter table t rename column fun to func;
        sokrates@11.2 > desc t
         Name                                      Null?    Type
         ----------------------------------------- -------- ------------------
         I                                                  NUMBER(38)
         FUNC                                               NUMBER
         sokrates@11.2 > exec replay_table_metadata ( 'T' )
           (    "I" NUMBER(*,0),
          TABLESPACE "USERS"  ...
        BEGIN replay_table_metadata ( 'T' ); END;
        ERROR at line 1:
        ORA-54012: virtual column is referenced in a column expression
        ORA-06512: at "SOKRATES.REPLAY_TABLE_METADATA", line 7
        ORA-06512: at line 1


        I bet there are another tons of other bugs around with virtual columns

        Comment by Matthias Rogel — November 29, 2013 @ 11:33 am GMT Nov 29,2013 | Reply

        • Matthias,

          Nice exanple. I am, as ever, impressed by your skill in finding holes in the Oracle implementation of its new features.

          Comment by Jonathan Lewis — December 10, 2013 @ 9:37 am GMT Dec 10,2013

    • Babak,

      Thanks for the observations – very good point, and a lovely example of new features not mixing with existing features. I guess the problem must have been around since 8i when function-based indexes were first introduced.

      I don’t have an answer to your question, possibly a detailed examination of expdp and impdp would help. I could imagine some awkward problems with dependency loops, however, that could only be solved by some awkward manual intervention. I’ve posted a twitter comment linking to this question.

      Comment by Jonathan Lewis — November 29, 2013 @ 9:37 am GMT Nov 29,2013 | Reply

      • Thanks Matthias for the clarification, and Jonathan for putting it on Twitter.

        I’d like to share the communication I’ve received from “my source in Oracle” (Sorry, a decade in journalism scars anyone’s soul, let alone his literature manners!):

        “In releases prior to, a table with a virtual column based on the result of a PL/SQL function would not be recreated successfully on Import, even when REMAP_SCHEMA was not used. This is because the necessary PL/SQL function would not have been created before the table was created. The problem was addressed by modifying Import to create the table with a temporary definition for virtual columns, then creating the function, then modifying the definition of the virtual columns to use the correct function. The initial fix was in BUG#10186633 and this was later extended in BUG#14506804. The extended fix is also included in certain patch set updates and one-off patches for and

        Unfortunately this fix does not address the problem of REMAP_SCHEMA. Even in or, it is still possible to encounter a failure during Import, or a failure during runtime, depending on the privileges of the owner of the target schema and the existence of the source schema. I can see that there is a very similar enhancement request filed for function-based indexes….”


        Comment by Babak Tourani — December 11, 2013 @ 2:20 pm GMT Dec 11,2013 | Reply

  11. […] in many cases (especially since we might ne able to address the problem very efficiently using virtual columns), but it’s probably worth […]

    Pingback by Coalesce v. NVL | Oracle Scratchpad — February 13, 2018 @ 11:24 am GMT Feb 13,2018 | Reply

  12. […] said in the past that one of the best new features, in my view, in 11g was the appearance of proper virtual columns; and I’ve also been very keen on the new “approximate NDV” that makes it viable […]

    Pingback by Virtual bug | Oracle Scratchpad — June 13, 2018 @ 8:34 am BST Jun 13,2018 | Reply

  13. […] possible quick fix (that would require no changes to existing code) would be to create a couple of virtual columns (or extended stats) to represent the two expressions and gather stats on the resulting columns – though it is a […]

    Pingback by Cardinality Puzzle | Oracle Scratchpad — July 12, 2018 @ 12:58 pm BST Jul 12,2018 | Reply

RSS feed for comments on this post. TrackBack URI

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: