首页 > 数据库 > Oracle > 正文
Oracle 11g中 ADD COLUMN 功能增强 说明
2012-02-02       个评论      
收藏    我要投稿
一.  Enhanced ADD COLUMN 说明
Oracle 11gR1中,Oracle 对add column 进行了增强。 官网的说明地址:
http://docs.oracle.com/cd/B28359_01/server.111/b28279/chapter1.htm#NEWFTCH1
 
1.1 Enhanced ADD COLUMN Functionality
Default valuesof columns are maintained in the data dictionary for columns specifiedas NOT NULL.
--当列指定为not null,那么该列对应的默认值在数据字典中进行维护。
 
       Adding newcolumns with DEFAULT values and NOT NULL constraint nolonger requires the default value to be stored in all existing records. Thisnot only enables a schema modification in sub-seconds and independent of theexisting data volume, it also consumes no space.
       --添加一列,该列不空,且有默认值,在11g中不在需要存储这个默认在所有的记录中,该默认值单独保存在数据字典里,在使用时,在从数据字典中调用,从而减少了DDL操作的时间,也减少了空间的使用。
 
1.2 Adding Table Columns
To add a columnto an existing table, use the ALTERTABLE...ADD statement.
The followingstatement alters the hr.admin_emp table to add a new columnnamed bonus:
ALTER TABLE hr.admin_emp
     ADD (bonus NUMBER (7,2));
 
       If a new columnis added to a table, the column is initially NULL unless you specifythe DEFAULT clause. When you specify a default value, the databaseimmediately updates each row with the default value.
       --如果对表添加一个新列,那么在不指定default 值的情况下,该列初始化为NULL。 当我们指定默认值后,数据会立即更新该表中的所有记录。
 
       Note that thiscan take some time, and that during the update, there is an exclusive DML lockon the table. For some types of tables (for example, tables without LOBcolumns), if you specify both a NOT NULL constraint and adefault value, the database can optimize the column add operation and greatlyreduce the amount of time that the table is locked for DML.
       --注意,这个更新操作可能需要很多时间,并且在表上还会添加一个排它锁。
 
You can add acolumn with a NOT NULL constraint only if the table does notcontain any rows, or you specify a default value.
--我们仅可以在表中没有记录或者指定默认值的情况下才可以使用NOT NULL 限制。
 
1.3 说明
       通过上面的说明,对add column 有了一定的了解。在Oracle 11g 以前,如果我们要添加一列,且该列不为空,那么需要指定默认值,如果表非常大,那么在执行时,在添加列之后,更新表中所有的记录,添加新的默认值。这样会花费很长的时间,同时也会产生大量的redo log。 所以在11g以前添加带默认值的列需要在DB 相对空闲时进行。
 
       在Oracle 11g对add column功能进行了增强,在上述情况下,11g中不会更新表中所有的记录,而是将默认值保存到数据字典里。 当用户查询该列的记录时,在从数据字典(sys.col$.default$)中获取默认值。 这样做可以减少系统的开销。
 
注意:
       在第一添加列是,会同时更新ecol$和col$ 字典,但是如果以后修改这个默认值,就仅修改col$中的值,我们以后的查询也是从col$中获取,而ecol$中,则永远保存的是我们第一次赋予的default值。
 
MOS上与该功能相关的一个BUG 说明:
       Wrong Result For Added Column After TableCreation in 11g [ID 1106553.1]
 
二.示例
 
2.1 add column 操作示例
SQL> select * from v$version;
 
BANNER
----------------------------------------------------------------------
Oracle Database 11g Enterprise EditionRelease 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE   11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0- Production
NLSRTL Version 11.2.0.1.0 – Production
 
SQL> create table t1(id number,namevarchar2(20));
Table created.
SQL> insert into t1 values(1,'dave');
1 row created.
SQL> insert into t1 values(2,'anqing');
1 row created.
SQL> insert into t1values(3,'huaining');
1 row created.
 
SQL> commit;
Commit complete.
 
SQL> select * from t1;
       ID NAME
---------- -----------------
        1 dave
        2 anqing
        3 huaining
 
在执行add column 之前,我们启用10046 事件跟踪一下这个过程:
 
SQL> oradebug setmypid
Statement processed.
SQL> oradebug event 10046 trace name context forever,level 8;
Statement processed.
 
--执行操作
SQL> alter table t1 add tel varchar2(20)default '13888888888' not null;
Table altered.
 
SQL> select * from t1;
 
       ID NAME                 TEL
---------- ----------------------------------------
        1 dave                 13888888888
        2 anqing               13888888888
        3 huaining             13888888888
 
--关闭10046event,并查看trace:
SQL> oradebug event 10046 trace namecontext off;
Statement processed.
SQL> oradebug tracefile_name
d:\app\administrator\diag\rdbms\newccs\newccs\trace\newccs_ora_308.trc
 
关于10046 事件的更多说明,参考我的Blog:
Oracle SQLTrace 和10046事件
http://blog.csdn.net/tianlesoftware/article/details/5857023
 
2.2 分析trace 文件
 
查看newccs_ora_308.trc文件里的内容,搜索一下:
=====================
PARSING IN CURSOR #11 len=445 dep=1 uid=0oct=6 lid=0 tim=5734874878 hv=1706555580 ad='b61eda64' sqlid='dbcjnkpkvgy5w'
update col$ setname=:3,segcol#=:4,type#=:5,length=:6,precision#=decode(:5,182/*DTYIYM*/,:7,183/*DTYIDS*/,:7,decode(:7,0,null,:7)),scale=decode(:5,2,decode(:8,-127/*MAXSB1MINAL*/,null,:8),178,:8,179,:8,180,:8,181,:8,182,:8,183,:8,231,:8,null),null$=:9,fixedstorage=:10,segcollength=:11,col#=:12,property=:13,charsetid=:14,charsetform=:15,spare1=:16,spare2=:17,spare3=:18,deflength=decode(:19,0,null,:19),default$=:20where obj#=:1 and intcol#=:2
END OF STMT
PARSE#11:c=0,e=857,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=5734874874
EXEC#11:c=15600,e=23270,p=0,cr=2,cu=7,mis=1,r=1,dep=1,og=4,plh=511615611,tim=5734898508
STAT #11 id=1 cnt=0 pid=0pos=1 obj=0 op='UPDATE  COL$ (cr=2 pr=0pw=0 time=0 us)'
STAT #11 id=2 cnt=1 pid=1 pos=1 obj=50op='INDEX UNIQUE SCAN I_COL3 (cr=2 pr=0 pw=0 time=0 us cost=1 size=59 card=1)'
CLOSE#11:c=0,e=4,dep=1,type=3,tim=5734898829
=====================
PARSING IN CURSOR #4 len=37 dep=1 uid=0oct=2 lid=0 tim=5734910715 hv=4050124187 ad='b61ed628' sqlid='cqrnq6vsqgzcv'
insert into ecol$ values(:1, :2, :3)
END OF STMT
PARSE#4:c=0,e=578,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=5734910712
EXEC#4:c=0,e=973,p=0,cr=2,cu=3,mis=1,r=1,dep=1,og=4,plh=0,tim=5734912051
STAT #4 id=1 cnt=0 pid=0 pos=1 obj=0op='LOAD TABLE CONVENTIONAL  (cr=2 pr=0pw=0 time=0 us)'
CLOSE#4:c=0,e=4,dep=1,type=3,tim=5734912234
=====================
PARSING IN CURSOR #10 len=97 dep=1 uid=0oct=3 lid=0 tim=5734913014 hv=2759248297 ad='b61ed1ac' sqlid='aa35g82k7dkd9'
select binaryDefVal,length(binaryDefVal) from ecol$          where tabobj# = :1 and colnum = :2
END OF STMT
PARSE#10:c=0,e=596,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=5734913010
EXEC#10:c=0,e=1149,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=3081038021,tim=5734914364
FETCH#10:c=0,e=65,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=3081038021,tim=5734914511
STAT #10 id=1 cnt=1 pid=0 pos=1 obj=123op='TABLE ACCESS BY INDEX ROWID ECOL$ (cr=2 pr=0 pw=0 time=0 us cost=1size=2028 card=1)'
STAT #10 id=2 cnt=1 pid=1 pos=1 obj=126op='INDEX RANGE SCAN ECOL_IX1 (cr=1 pr=0 pw=0 time=0 us cost=1 size=0 card=1)'
CLOSE#10:c=0,e=4,dep=1,type=3,tim=5734914692
 
格式有点乱,使用tkprof 格式化一下:
C:\Users\Administrator.DavidDai>tkprof d:\app\administrator\diag\rdbms\newccs\newccs\trace\newccs_ora_308.trc d:\dave.txt
 
TKPROF: Release 11.2.0.1.0 - Development onWed Feb 1 22:09:55 2012
 
Copyright (c) 1982, 2009, Oracle and/or itsaffiliates.  All rights reserved.
 
查找的相关结果:
SQL ID: 60uw2vh6q9vn2
Plan Hash: 0
insert intocol$(obj#,name,intcol#,segcol#,type#,length,precision#,scale,
 null$,offset,fixedstorage,segcollength,deflength,default$,col#,property,
 charsetid,charsetform,spare1,spare2,spare3)
values
(:1,:2,:3,:4,:5,:6,decode(:5,182/*DTYIYM*/,:7,183/*DTYIDS*/,:7,decode(:7,0,
 null,:7)),decode(:5,2,decode(:8,-127/*MAXSB1MINAL*/,null,:8),178,:8,179,:8,
 180,:8,181,:8,182,:8,183,:8,231,:8,null),:9,0,:10,:11,decode(:12,0,null,:12)
 ,:13,:14,:15,:16,:17,:18,:19,:20)
 
SQL ID: dbcjnkpkvgy5w
Plan Hash: 511615611
update col$ setname=:3,segcol#=:4,type#=:5,length=:6,precision#=decode(:5,
 182/*DTYIYM*/,:7,183/*DTYIDS*/,:7,decode(:7,0,null,:7)),scale=decode(:5,2,
 decode(:8,-127/*MAXSB1MINAL*/,null,:8),178,:8,179,:8,180,:8,181,:8,182,:8,
 183,:8,231,:8,null),null$=:9,fixedstorage=:10,segcollength=:11,col#=:12,
 property=:13,charsetid=:14,charsetform=:15,spare1=:16,spare2=:17,spare3=:18,
 deflength=decode(:19,0,null,:19),default$=:20
where obj#=:1 and intcol#=:2
 
SQL ID: cqrnq6vsqgzcv
Plan Hash: 0
insert into ecol$ values (:1, :2, :3)
 
SQL ID: aa35g82k7dkd9
Plan Hash: 3081038021
select binaryDefVal, length(binaryDefVal)
from ecol$ where tabobj# = :1 and colnum =:2
--注意这里ecol$和col$ 都被更新了。
 
2.3 测试ecol$ 和col$ 区别:
 
查看sys.ecol$
SQL> select * from sys.ecol$;
 
  TABOBJ#     COLNUM     BINARYDEFVAL
---------- ---------------------------------------
    83210          3       3133383838383838383838
 
SQL> desc ecol$;
 Name                                     Null?    Type
 ------------------------------------------------- -------
 TABOBJ#                                            NUMBER
 COLNUM                                            NUMBER
 BINARYDEFVAL                                       BLOB
 
注意这里的最后值是BLOB的。
 
我可以使用如下SQL 查看对应的值:
SQL> select utl_raw.cast_to_varchar2(dbms_lob.substr(BINARYDEFVAL)) AS TEL from sys.ecol$;
TEL
--------------------------------------------------------------------------------
13888888888
 
这里返回的是我们之前的设置,关于LOB的更多内容参考我的Blog:
ORACLE LOB 大对象处理 /database/201202/118146.html
 
通过sys.col$就看的比较简单了:
SQL> select obj#,name,default$ fromsys.col$ where obj#=83210;
 
     OBJ# NAME                           DEFAULT$
---------- ----------------------------------------------------
    83210 ID
    83210 NAME
    83210 TEL                           '13888888888'
 
现在我们来修改这个字段的默认值:
SQL> alter table t1 modify tel default '13899999999';
Table altered.
 
SQL> select * from t1;
 
       ID NAME                 TEL
---------- ----------------------------------------
        1 dave                 13888888888
        2 anqing               13888888888
        3 huaining             13888888888
 
SQL> insert into t1(id,name)values(4,'hefei');
1 row created.
 
SQL> select * from t1;
 
       ID NAME                 TEL
---------- ----------------------------------------
        1 dave                 13888888888
        2 anqing               13888888888
        3 huaining             13888888888
        4 hefei                13899999999
 
SQL> commit;
Commit complete.
 
再次查看ecol$ 和col$ 中对应的记录:
SQL> select obj#,name,default$ fromsys.col$ where obj#=83210;
 
     OBJ# NAME                           DEFAULT$
---------- ----------------------------------------------------
    83210 ID
    83210 NAME
    83210 TEL                           '13899999999'
--col$ 中变成了13899999999
 
SQL> selectutl_raw.cast_to_varchar2(dbms_lob.substr(BINARYDEFVAL)) AS TEL from sys.ecol$;
TEL
--------------------------------------------------------------------------------
13888888888
--注意这里,ecol$中的值并没有改变。
 
       这就验证了我们之前的说明,第一赋予的默认值,将永久的保存在ecol$中,如果我们修改这个默认值,那么,那么仅修改col$中的值,我们查询时也是从这个数据字典里取数据。
 
 
 
 
 
-------------------------------------------------------------------------------------------------------
版权所有,文章允许转载,但必须以链接方式注明源地址,否则追究法律责任!
Email:   tianlesoftware@gmail.com
Skype: tianlesoftware
Blog:     http://www.tianlesoftware.com
Weibo: http://weibo.com/tianlesoftware
Twitter: http://twitter.com/tianlesoftware
Facebook:http://www.facebook.com/tianlesoftware
点击复制链接 与好友分享!回本站首页
相关TAG标签 功能
上一篇:ORACLE LOB 大对象处理
下一篇:Oracle ADF基于AM的两种回滚写法及切换行事件(附代码)
相关文章
图文推荐
文章
推荐
点击排行

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训
版权所有: 红黑联盟--致力于做实用的IT技术学习网站