在oracle 11g以前,如果要在一个大表中增加一列,并设置默认值,那将是一个非常悲剧的事情.有些时候不得不选择在线重定义功能来实现
oracle 11g增加列,并带默认值的新特性
[日期：2014-04-05]来源：linux社区 作者：linux[字体：]
在oracle 11g以前,如果要在一个大表中增加一列,并设置默认值,那将是一个非常悲剧的事情.有些时候不得不选择在线重定义功能来实现该需求.而在11g中增加新列并设置默认值,只是简单的修改数据字典来实现该功能,大大提供效率
10g加列(默认值)
sql> select * from v$version;
banner
----------------------------------------------------------------
oracle database 10g enterprise edition release 10.2.0.1.0 - prod
pl/sql release 10.2.0.1.0 - production
core 10.2.0.1.0 production
tns for linux: version 10.2.0.1.0 - production
nlsrtl version 10.2.0.1.0 - production
sql> create table t_xifenfei
2 as select object_id,object_name from dba_objects;
table created.
sql> select count(*) from t_xifenfei;
count(*)
----------
49827
sql> desc t_xifenfei
name null? type
----------------------------------------- -------- ----------------------------
object_id number
object_name varchar2(128)
sql> set timing on
sql> alter table t_xifenfei add c_xff varchar2(100) default 'www.xifenfei.com' not null;
table altered.
elapsed: 00:00:06.13
--使用了6秒钟
sql> select rowid,
2 dbms_rowid.rowid_relative_fno(rowid)rel_fno,
3 dbms_rowid.rowid_block_number(rowid)blockno,
4 dbms_rowid.rowid_row_number(rowid) rowno
5 from t_xifenfei where object_name='obj$';
rowid rel_fno blockno rowno
------------------ ---------- ---------- ----------
aaamwjaaeaaaab8aar 4 124 43
sql> alter system dump datafile 4 block 124;
system altered.
elapsed: 00:00:00.08
11g增加列(默认值)
sql> select * from v$version;
banner
-----------------------------------------------------------------------
oracle database 11g enterprise edition release 11.2.0.3.0 - production
pl/sql release 11.2.0.3.0 - production
core 11.2.0.3.0 production
tns for linux: version 11.2.0.3.0 - production
nlsrtl version 11.2.0.3.0 - production
sql> create table t_xifenfei
2 as select object_id,object_name from dba_objects;
table created.
sql> select count(*) from t_xifenfei;
count(*)
----------
74605
sql> select rowid,
2 dbms_rowid.rowid_relative_fno(rowid)rel_fno,
3 dbms_rowid.rowid_block_number(rowid)blockno,
4 dbms_rowid.rowid_row_number(rowid) rowno
5 from t_xifenfei where object_name='obj$';
rowid rel_fno blockno rowno
------------------ ---------- ---------- ----------
aaaspraaeaaaacraau 4 171 46
sql> alter system dump datafile 4 block 171;
system altered.
sql> set timing on
sql> alter table t_xifenfei add c_xff varchar2(100) default 'www.xifenfei.com' not null;
table altered.
elapsed: 00:00:00.19
--只是使用了0.19秒
sql> select rowid,
2 dbms_rowid.rowid_relative_fno(rowid)rel_fno,
3 dbms_rowid.rowid_block_number(rowid)blockno,
4 dbms_rowid.rowid_row_number(rowid) rowno
5 from t_xifenfei where object_name='obj$';
rowid rel_fno blockno rowno
------------------ ---------- ---------- ----------
aaaspraaeaaaacraau 4 171 46
elapsed: 00:00:00.04
sql> alter system dump datafile 4 block 171;
system altered.
通过10g和11g的增加相同列和默认值的对比可以发现,11g的速度要比10g快很多很多,下面我们通过上面dump出来相关的数据块来分析原因
dump分析
11g增加列之前dump
tab 0, row 0, @0x1f74
tl: 12 fb: --h-fl-- lb: 0x0 cc: 2
col 0: [ 2] c1 15
col 1: [ 5] 49 43 4f 4c 24
tab 0, row 1, @0x1f66
tl: 14 fb: --h-fl-- lb: 0x0 cc: 2
col 0: [ 2] c1 2f
col 1: [ 7] 49 5f 55 53 45 52 31
tab 0, row 2, @0x1f5b
tl: 11 fb: --h-fl-- lb: 0x0 cc: 2
col 0: [ 2] c1 1d
col 1: [ 4] 43 4f 4e 24
11g增加列之后dump
tab 0, row 0, @0x1f74
tl: 12 fb: --h-fl-- lb: 0x0 cc: 2
col 0: [ 2] c1 15
col 1: [ 5] 49 43 4f 4c 24
tab 0, row 1, @0x1f66
tl: 14 fb: --h-fl-- lb: 0x0 cc: 2
col 0: [ 2] c1 2f
col 1: [ 7] 49 5f 55 53 45 52 31
tab 0, row 2, @0x1f5b
tl: 11 fb: --h-fl-- lb: 0x0 cc: 2
col 0: [ 2] c1 1d
col 1: [ 4] 43 4f 4e 24
tab 0, row 3, @0x1f4f
tl: 12 fb: --h-fl-- lb: 0x0 cc: 2
col 0: [ 2] c1 10
col 1: [ 5] 55 4e 44 4f 24
10g增加列之后dump
因为10g在没有增加列之前的dump和11g未增加列之前类似,所以未dump出来
tab 0, row 0, @0x1f63
tl: 29 fb: --h-fl-- lb: 0x2 cc: 3
col 0: [ 2] c1 15
col 1: [ 5] 49 43 4f 4c 24
col 2: [16] 77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d
tab 0, row 1, @0x1f44
tl: 31 fb: --h-fl-- lb: 0x2 cc: 3
col 0: [ 2] c1 2d
col 1: [ 7] 49 5f 55 53 45 52 31
col 2: [16] 77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d
tab 0, row 2, @0x1f28
tl: 28 fb: --h-fl-- lb: 0x2 cc: 3
col 0: [ 2] c1 1d
col 1: [ 4] 43 4f 4e 24
col 2: [16] 77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d
tab 0, row 3, @0x1f0b
tl: 29 fb: --h-fl-- lb: 0x2 cc: 3
col 0: [ 2] c1 10
col 1: [ 5] 55 4e 44 4f 24
col 2: [16] 77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d