<rt id="bn8ez"></rt>
<label id="bn8ez"></label>

  • <span id="bn8ez"></span>

    <label id="bn8ez"><meter id="bn8ez"></meter></label>

    隨筆 - 6  文章 - 129  trackbacks - 0
    <2025年5月>
    27282930123
    45678910
    11121314151617
    18192021222324
    25262728293031
    1234567

    常用鏈接

    留言簿(14)

    隨筆檔案(6)

    文章分類(467)

    文章檔案(423)

    相冊

    收藏夾(18)

    JAVA

    搜索

    •  

    積分與排名

    • 積分 - 825418
    • 排名 - 49

    最新評論

    閱讀排行榜

    評論排行榜

    原文  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 閱讀(2812) 評論(0)  編輯  收藏 所屬分類: oracle
    主站蜘蛛池模板: 亚洲人成欧美中文字幕| 国产成人无码区免费内射一片色欲 | 免费无码作爱视频| 亚洲色大成网站www永久| 暖暖免费高清日本中文| a毛片在线免费观看| 亚洲人精品亚洲人成在线| 国产亚洲精品资在线| 国产又黄又爽又猛免费app| fc2免费人成为视频| 亚洲资源最新版在线观看| 亚洲中文字幕成人在线| 一个人看www在线高清免费看| 一级毛片免费一级直接观看| 亚洲人成影院午夜网站| 久久99亚洲综合精品首页| 24小时免费直播在线观看| 久久精品成人免费看| 美国毛片亚洲社区在线观看| 久久综合亚洲色一区二区三区| 国产男女猛烈无遮挡免费视频网站 | 无码天堂亚洲国产AV| 亚洲精品国产肉丝袜久久| 亚洲一区二区高清| 在线视频免费观看www动漫| 久久免费观看国产精品| 色屁屁www影院免费观看视频| 亚洲性一级理论片在线观看| 亚洲无码在线播放| 免费永久国产在线视频| 久草在视频免费福利| 日本视频免费高清一本18| 亚州**色毛片免费观看| 亚洲人成电影网站色| 亚洲三级在线播放| 亚洲视频在线观看一区| 亚洲欧洲日产国码无码网站| 亚洲成A人片77777国产| 国产婷婷高清在线观看免费| 午夜宅男在线永久免费观看网| 永久黄色免费网站|