Oracle Scratchpad

April 16, 2009

Virtual Columns

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

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]

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

16 Comments »

  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,
    Dani

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

    http://hourim.wordpress.com/2013/08/07/indexed-virtual-column-and-dml-error-logging-ora-03113-end-of-file-on-communication-channel/

    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 12.1.0.1.0 - 64bit Production              0
    PL/SQL Release 12.1.0.1.0 - Production                                                    0
    CORE	12.1.0.1.0	Production                                                            0
    TNS for 64-bit Windows: Version 12.1.0.1.0 - Production                                   0
    NLSRTL Version 12.1.0.1.0 - 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.

    Solutions:
    – 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).

    Regards,

    Comment by Babak Tourani — November 20, 2013 @ 10:06 am BST 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.

      Regards,

      Comment by Babak Tourani — November 21, 2013 @ 11:10 am BST 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;
        begin
           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' )
        
          CREATE TABLE "SOKRATES"."T"
           (    "I" NUMBER(*,0)
           ) SEGMENT CREATION DEFERRED
          PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
          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' )
        
          CREATE TABLE "SOKRATES"."T"
           (    "I" NUMBER(*,0),
                "FUNC" NUMBER GENERATED ALWAYS AS ("SOKRATES"."P"."FUNC"()) VIRTUAL VISIBLE
           ) SEGMENT CREATION DEFERRED
          PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
          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
        
        

        see http://marogel.wordpress.com/2012/09/03/ora-54012-oddity-and-a-workaround/

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

        Comment by Matthias Rogel — November 29, 2013 @ 11:33 am BST 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 BST 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 BST 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 11.2.0.4, 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 11.2.0.2 and 11.2.0.3.

        Unfortunately this fix does not address the problem of REMAP_SCHEMA. Even in 11.2.0.4.0 or 12.1.0.1.0, 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….”

        Regards,
        Babak.

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


RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,258 other followers