Oracle 11g增加列,并带默认值的新特性

xiongfei0

2014-04-05

在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

对比发现11g在增加列之后,以前的数据dump出来的内容未有任何改变.也就是说:在10g中,我们增加一个列和默认值,会自动的增加到真实的数据中,而在11g中增加列和默认值并未真的加到11g的表中已经存在的数据中.
11g中插入新数据dump测试

SQL> insert into chf.t_xifenfei(object_id,object_name)
2 select object_id,object_name from dba_objects;
 
74605 rows created.
 
SQL> commit;
 
commit complete.
 
SQL> alter SYSTEM FLUSH BUFFER_CACHE;
 
System altered.
 
SQL> /
 
System altered.
--建议刷新
 
SQL> 
SQL> 
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 chf.t_xifenfei where object_name='OBJ$';
 
ROWID REL_FNO BLOCKNO ROWNO
------------------ ---------- ---------- ----------
AAASpRAAEAAAACrAAu 4 171 46
AAASpRAAEAAAB5TAAu 4 7763 46
 
SQL> alter SYSTEM DUMP DATAFILE 4 BLOCK 7763;
 
System altered.
 
--dump内容
tab 0, row 0, @0x4e3
tl: 29 fb: --H-FL-- lb: 0x1 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, @0x500
tl: 31 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 2] c1 2f
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, @0x51f
tl: 28 fb: --H-FL-- lb: 0x1 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, @0x53b
tl: 29 fb: --H-FL-- lb: 0x1 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

通过这里看看出:在11g中后续插入的数据,默认值也插入到数据文件中

line oracle

安科网

Oracle 11g增加列,并带默认值的新特性

xiongfei0

xiongfei0

相关推荐

了解这些操作，Python中99%的文件操作都将变得游刃有余！

shell while 双循环对比查找共有字段

Python-闭包

数据处理：oltp和olap

启动django报错

python读取大词向量文件

Python Basic - 练习-提示用户输入长度跟宽度，然后输出字符“0”描述出一个正方形

Shell脚本批量修改文件编码为UTF-8

python 逐行读取文件的几种方法

python with (as)语句

kafka 配置文件参数详解

python--读取excel通过django框架入库mysql(完整代码)

python逐行读取文件内容的三种方法

linux中按行读取指定行

Mac gyp: No Xcode or CLT version detected!

PHP Warning: PHP Startup: in Unknown on line 0

Python3并发写文件与Python对比

更新 Ubuntu 12.04 内核至 3.8.0

line-clamp无法生效的解决方案

Twitter的分布式自增ID算法Snowflake

xiongfei0