欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

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

程序员文章站 2024-01-28 12:54:52
...

在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