oracle11g中add column功能增强

一. Enhanced ADD COLUMN 说明
在Oracle 11gR1中，Oracle 对add column 进行了增强。 官网的说明地址：
https://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:appadministratordiag
dbms
ewccs
ewccs race
ewccs_ora_308.trc

关于10046 事件的更多说明，参考我的Blog：
Oracle SQLTrace 和10046事件
https://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:UsersAdministrator.DavidDai>tkprof d:appadministratordiag
dbms
ewccs
ewccs race
ewccs_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 大对象处理 https://www.2cto.com/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$中的值，我们查询时也是从这个数据字典里取数据。

声明: 除非转自他站（如有侵权，请联系处理）外，本文采用 BY-NC-SA 协议进行授权 | 智乐兔
转载请注明：转自《oracle11g中add column功能增强》
本文地址：https://www.zhiletu.com/archives-301.html
关注公众号：