原文 http://blogs.oracle.com/sql/entry/ora_54033_and_the_hidden
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!
posted on 2015-09-16 14:30
Ke 閱讀(2824)
評論(0) 編輯 收藏 所屬分類:
oracle