X

Celebrating the joy and power of Oracle SQL with the Oracle Developer Advocate team

  • July 21, 2015

ORA-54033 and the Hidden Virtual Column Mystery

Chris Saxon
Developer Advocate

A colleague recently asked me a question:

"I'm modifying the data type of a column. When doing so I get the following error:
ORA-54033: column to be modified is used in a virtual column expression
But there's no virtual columns defined on the table! What on earth's going on?!"

This was exceptionally confusing. Looking at the table definition we couldn't see any virtual columns defined:

create table tab (
  x integer, 
  y date, 
  z varchar2(30)
);

Sure enough, when we tried to change the data type of y we got the exception:

alter table tab modify (y timestamp);

ORA-54033: column to be modified is used in a virtual column expression

How could this be?

Perhaps there was a column defined that we couldn't see. Querying user_tab_cols revealed something interesting:

select column_name, data_default, hidden_column 
from   user_tab_cols
where  table_name = 'TAB';

COLUMN_NAME                    DATA_DEFAULT                   HID
-----------	               ------------                   ---
SYS_STUYPW88OE302TFVBNC6$MMQXE SYS_OP_COMBINED_HASH("X","Y"   YES
Z                                                             NO
Y                                                             NO
X                                                             NO

The SYS_... column isn't in the table DDL! Where does it come from? And what's SYS_OP_COMBINED_HASH all about? Has someone been mucking around with the database?

The SYS_ prefix is a sign that the column is system generated. So something's happened that's caused Oracle to create this on our behalf.

SYS_OP_COMBINED_HASH is an undocumented feature. The name implies Oracle is merging the arguments together to form a hash.

Is there a feature where we want to capture information about a group of columns?

Indeed there is - extended statistics! This feature enables to Oracle calculate statistics on a group of columns. It uses this information to improve row estimates. This is useful when there's a correlation between the values of two (or more) columns in a table.

Someone had created extended stats on this table for (x, y).

Now we've identified the problem, how do we get around it?

Simple: drop and recreate the extended stats:

exec dbms_stats.drop_extended_stats(user, 'tab', '(x, y)');

alter table tab modify (y timestamp);

select dbms_stats.create_extended_stats(user, 'tab', '(x, y)')
from   dual;

DBMS_STATS.CREATE_EXTENDED_STATS(USER,'TAB','(X,Y)')                           
--------------------------------------------------------------------
SYS_STUYPW88OE302TFVBNC6$MMQXE  

Success!

Extended stats are a great way to improve the optimizer's row estimates. If you need to create these, I recommend you also do the following:

  • Apply the extended stats to all environments
  • Put a comment on the columns explaining what you've done, e.g.
    • comment on column tab.x is 'part of extended stats. To modify data type drop and recreate stats';

These actions will help prevent future developers getting stuck tracking down the cause of "missing" virtual columns!

Join the discussion

Comments ( 18 )
  • Natalka Roshak Wednesday, July 22, 2015

    This post is bound to save some folks with extended stats several hours of hair-pulling!

  • Chris Saxon Wednesday, July 22, 2015

    I hope so, we were certainly baffled for some time!

  • guest Monday, October 26, 2015

    This is gr8 input ..Thanks team

  • Jason Monday, May 2, 2016

    Is this going to happen every time you try to modify column with extended stats on it? I tried this in our environment (oracle 12c), but the DDL went through without issue.

  • Chris Saxon Tuesday, May 3, 2016

    Jason - What exactly did you do? It's only an issue if you have extended stats on the column(s) you're changing. I get this in 12c:

    Connected to:
    Oracle Database 12c EE Extreme Perf Release 12.1.0.2.0 - 64bit Production
    With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics
    and Real Application Testing options

    SQL>
    SQL>
    SQL> create table tab (
    2 x integer,
    3 y date,
    4 z varchar2(30)
    5 );

    Table created.

    Elapsed: 00:00:00.26
    SQL>
    SQL> select dbms_stats.create_extended_stats(user, 'tab', '(x, y)')
    2 from dual;

    DBMS_STATS.CREATE_EXTENDED_STATS(USER,'TAB','(X,Y)')
    -------------------------------------------------------------------------

    SYS_STUYPW88OE302TFVBNC6$MMQXE

    Elapsed: 00:00:00.51
    SQL>
    SQL> alter table tab modify (y timestamp);
    alter table tab modify (y timestamp)
    *
    ERROR at line 1:
    ORA-54033: column to be modified is used in a virtual column expression

  • Jason Tuesday, May 3, 2016

    Hi Chris,

    We had an issue where we have varchar2(1) and we have 2 extended stats on the column and when we tried to modify it to varchar2(2). It failed with ORA-54033. But when I tried to test it myself with only column length change, I couldn't reproduce it.

    Connected to:
    Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
    With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
    Advanced Analytics and Real Application Testing options

    SQL> create table tab (x varchar2(10), y varchar2(10));

    Table created.

    SQL> select dbms_stats.create_extended_stats(user, 'tab', '(x,y)') from dual;

    DBMS_STATS.CREATE_EXTENDED_STATS(USER,'TAB','(X,Y)')
    --------------------------------------------------------------------------------
    SYS_STUYPW88OE302TFVBNC6$MMQXE

    SQL> alter table tab modify (x varchar2(20));

    Table altered.

  • Chris Saxon Tuesday, May 3, 2016

    Interesting. I get the same when running your example - i.e. it runs without error.

    I'm not sure why it's working in some cases and not others.

  • guest Thursday, September 1, 2016

    Chirs - Thanks for the excellent information/explanation.

    <i>I'm not sure why it's working in some cases and not others. </i>

    got why it is working on some cases and not others.

    Incase of changing the datatype from one family to other family, we get this error. ( I am on 12.1.0.2 )

    demo@ORA12C>
    demo@ORA12C> create table t(x int,y varchar2(10), z int);

    Table created.

    demo@ORA12C> variable x varchar2(100)
    demo@ORA12C> exec :x := dbms_stats.create_extended_stats(user, 'T', '(x,y)');

    PL/SQL procedure successfully completed.

    demo@ORA12C> alter table t modify y timestamp;
    alter table t modify y timestamp
    *
    ERROR at line 1:
    ORA-54033: column to be modified is used in a virtual column expression

    But in case of increasing the length like this, no errors.

    demo@ORA12C> drop table t purge;

    Table dropped.

    demo@ORA12C> create table t(x int,y varchar2(10),z int);

    Table created.

    demo@ORA12C> variable x varchar2(100)
    demo@ORA12C> exec :x := dbms_stats.create_extended_stats(user, 'T', '(x,y)');

    PL/SQL procedure successfully completed.

    demo@ORA12C> alter table t modify y varchar2(20);

    Table altered.

    demo@ORA12C>

  • Chris Saxon Thursday, September 1, 2016

    Thanks for figuring that out :)

  • guest Friday, November 25, 2016

    ORA-54033 also occurs when you RENAME a column with Extended Stats.

  • Chris Saxon Friday, November 25, 2016

    Didn't know that; that for letting us know about rename too!

  • guest Tuesday, February 28, 2017

    If you use an OLH (hive table to oracle table load) with the column auto-mapping option, then the load starts failing after the extended stats gathered. The extended stats creates a system virtual column in the table and keeps it in the Oracle data dictionary. The OLH auto-mapping considers this column as a part of Oracle table and tries to find the corresponding column in Hive.

  • Chris Saxon Tuesday, February 28, 2017

    Thanks for the info on OLH. Are you saying that also causes the issue we've seen here?

  • guest Tuesday, February 28, 2017

    The OLH failing with the different error code:
    ERROR loader.LoaderMetadata: unsupported load target column "SYS_STS$JTKTHTUPH4ER2HETTF#RIY"
    oracle.hadoop.loader.OraLoaderException: unsupported load target column "SYS_STS$JTKTHTUPH4ER2HETTF#RIY - virtual column"

  • Chris Saxon Wednesday, March 1, 2017

    OK, so what's that got to do with this post?

  • Gaurav Thursday, June 22, 2017
    how do we disable creation of extended stats altogether ?
  • Alex Monday, September 30, 2019
    Thanks Chris - this post is still helpful 4+ years later.
  • Chris Saxon Tuesday, October 1, 2019
    Thanks Alex :)
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.