Oracle DDL性能改进-大表新增列行为分析

生产过程中常常会遇到对表需要在线增加列，尤其是新增带缺省值的非空列，例如：

ALTER TABLE .. ADD COLUMN ( ... NOT NULL DEFAULT ... )

在Oracle 11g以前，执行这条DDL会涉及以下步骤：

  1. 在数据字典中中增加新列定义：列类型、长度、NOT NULL及DEFAULT等；
  2. 更新表上所有行，使用新列的缺省值增加新列的值。
    对百万级以上的大表，该DDL可能会耗费较长时间及系统资源，同时也会对该表进行长时间加锁，影响业务的正常使用。

Oracle 11g起引入了新的隐藏参数：_ADD_COL_OPTIM_ENABLED。如果数据库参数：COMPATIBLE>=11时，则_ADD_COL_OPTIM_ENABLED缺省为TRUE。
_ADD_COL_OPTIM_ENABLED值为True时，新增带缺省值非空列的DDL会仅仅将新列添加到表定义中，但是不会更新实际的表行记录。 内部会在数据字典中存储一个标志，对于没有列值的行均会返回DEFAULT值。 对于大表，这会节省大量资源和加快执行速度。 在数据库内部，访问该表的任何代码路径都会查询数据字典设置，为该列返回正确的数据。该 参数的设置仅影响新的“ ADD COLUMN”命令。 一旦使用此种方式增加新的列，则数据字典中的标志将指示该特定列已被优化。可以通过以下查询获取已优化的列：

col object_name format a30
set lines 100
select owner, object_name, name 
from dba_objects, col$
where bitand(col$.PROPERTY,1073741824)=1073741824
    and object_id=obj#;

下面通过几个简单案例来分析下列值在数据块中的存储变化。

准备

测试数据库：11.2.0.4
compatible=11.2.0.4
_add_col_optim_enabled=true

SQL> create table test.t_obj(c1 varchar2(1)); 

Table created.

SQL> insert into test.t_obj values('1');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test.t_obj;

C
-
1

SQL> select dbms_rowid.rowid_object(rowid) obj#,
        dbms_rowid.rowid_relative_fno(rowid) rfile#,
        dbms_rowid.rowid_block_number(rowid) block#,
        dbms_rowid.rowid_row_number(rowid) row#                 
  from test.t_obj;  2    3    4    5  

      OBJ#     RFILE#     BLOCK#       ROW#
---------- ---------- ---------- ----------
     70623      4        134      0

SQL> alter system checkpoint;

System altered.

SQL> alter system dump datafile 4 block 134;

System altered.

查看trace文件：
Block header dump:  0x01000086
 Object id on Block? Y
 seg/obj: 0x113df  csc: 0x00.95330  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1000080 ver: 0x01 opc: 0
     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000a.011.000010a8  0x00c028a4.00cb.26  --U-    1  fsc 0x0000.00095331
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
bdba: 0x01000086
data_block_dump,data header at 0x7f96a71b3264
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x7f96a71b3264
     76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f93
avsp=0x1f7b
tosp=0x1f7b
0xe:pti[0]      nrow=1  offs=0
0x12:pri[0]     offs=0x1f93
block_row_dump:
tab 0, row 0, @0x1f93
tl: 5 fb: --H-FL-- lb: 0x1  cc: 1
col  0: [ 1]  31
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 134 maxblk 134

案例一：新增带缺省值的非空列

SQL> alter table test.t_obj add c2 varchar2(1) default 'a' not null;

Table altered.

SQL> col binarydefval format a10
select ec.* from sys.ecol$ ec,sys.obj$ o where ec.tabobj#=o.dataobj#;

SQL> 

   TABOBJ#     COLNUM BINARYDEFV
---------- ---------- ----------
     70623      2 61

SQL>  select c1,c2,dbms_rowid.rowid_object(rowid) obj#,
        dbms_rowid.rowid_relative_fno(rowid) rfile#,
        dbms_rowid.rowid_block_number(rowid) block#,
        dbms_rowid.rowid_row_number(rowid) row#                 
  from test.t_obj;  2    3    4    5  

C C   OBJ#     RFILE#     BLOCK#       ROW#
- - ---------- ---------- ---------- ----------
1 a  70623      4    134          0

SQL> col object_name format a30
set lines 100
select owner, object_name, name 
from dba_objects, col$
where bitand(col$.PROPERTY,1073741824)=1073741824
    and object_id=obj#;
    
OWNER                  OBJECT_NAME            NAME
------------------------------ ------------------------------ ------------------------------
TEST                   T_OBJ                  C2

SQL> alter system checkpoint;

System altered.

SQL> alter system dump datafile 4 block 134;

System altered.

查看trace文件：
Block header dump:  0x01000086
 Object id on Block? Y
 seg/obj: 0x113df  csc: 0x00.95330  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1000080 ver: 0x01 opc: 0
     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000a.011.000010a8  0x00c028a4.00cb.26  --U-    1  fsc 0x0000.00095331
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
bdba: 0x01000086
data_block_dump,data header at 0x7f96a71b3264
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x7f96a71b3264
     76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f93
avsp=0x1f7b
tosp=0x1f7b
0xe:pti[0]      nrow=1  offs=0
0x12:pri[0]     offs=0x1f93
block_row_dump:
tab 0, row 0, @0x1f93
tl: 5 fb: --H-FL-- lb: 0x1  cc: 1
col  0: [ 1]  31
end_of_block_dump

查询返回了期望的列值，但在数据块实际没有存储（根据Oracle内部机制，因为是NULL，所以未在数据块中存储）。

案例二：修改非空列的default

在案例一的基础上继续

SQL> alter table test modify (c2 default 'b');

Table altered.

SQL> select ec.* from sys.ecol$ ec,sys.obj$ o where ec.tabobj#=o.dataobj#;

   TABOBJ#     COLNUM BINARYDEFV
---------- ---------- ----------
     70623      2 61

SQL> select column_name,data_default from dba_tab_columns where owner='TEST'; 

COLUMN_NAME            DATA_DEFAULT
------------------------------ ---------------
C1
C2                 'b'

SQL> insert into test.t_obj(c1) values('2');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test.t_obj;

C C
- -
1 a
2 b

SQL> select c1,c2,dbms_rowid.rowid_object(rowid) obj#,
        dbms_rowid.rowid_relative_fno(rowid) rfile#,
        dbms_rowid.rowid_block_number(rowid) block#,
        dbms_rowid.rowid_row_number(rowid) row#                 
  from test.t_obj;  2    3    4    5  

C C   OBJ#     RFILE#     BLOCK#       ROW#
- - ---------- ---------- ---------- ----------
1 a  70623      4    134          0
2 b  70623      4    134          1

SQL> alter system checkpoint;

System altered.

SQL> alter system dump datafile 4 block 134;

System altered.

Block header dump:  0x01000086
 Object id on Block? Y
 seg/obj: 0x113df  csc: 0x00.95330  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1000080 ver: 0x01 opc: 0
     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000a.011.000010a8  0x00c028a4.00cb.26  --U-    1  fsc 0x0000.00095331
0x02   0x000a.01b.0000109b  0x00c028aa.00cb.03  --U-    1  fsc 0x0000.0009558d
bdba: 0x01000086
data_block_dump,data header at 0x7f96a71b3264
===============
tsiz: 0x1f98
hsiz: 0x16
pbl: 0x7f96a71b3264
     76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f8c
avsp=0x1f70
tosp=0x1f70
0xe:pti[0]      nrow=2  offs=0
0x12:pri[0]     offs=0x1f93
0x14:pri[1]     offs=0x1f8c
block_row_dump:
tab 0, row 0, @0x1f93
tl: 5 fb: --H-FL-- lb: 0x1  cc: 1
col  0: [ 1]  31
tab 0, row 1, @0x1f8c
tl: 7 fb: --H-FL-- lb: 0x2  cc: 2
col  0: [ 1]  32
col  1: [ 1]  62
end_of_block_dump

新插入的行，非空列使用了修改后的default值，已有行继续使用原有的default值。

案例三：将非空列去除非空属性

在案例二的基础上继续

SQL> alter table test.t_obj modify c2 null;

Table altered.

SQL> select ec.* from sys.ecol$ ec,sys.obj$ o where ec.tabobj#=o.dataobj#;

no rows selected

SQL> select column_name,data_default from dba_tab_columns where owner='TEST';

COLUMN_NAME            DATA_DEFAULT
------------------------------ ---------------
C1
C2                 'b'

SQL> select c1,c2,dbms_rowid.rowid_object(rowid) obj#,
        dbms_rowid.rowid_relative_fno(rowid) rfile#,
        dbms_rowid.rowid_block_number(rowid) block#,
        dbms_rowid.rowid_row_number(rowid) row#                 
  from test.t_obj;  2    3    4    5  

C C   OBJ#     RFILE#     BLOCK#       ROW#
- - ---------- ---------- ---------- ----------
1 a  70623      4    134          0
2 b  70623      4    134          1

SQL> alter system checkpoint;

System altered.

SQL> alter system dump datafile 4 block 134;

System altered.

Block header dump:  0x01000086
 Object id on Block? Y
 seg/obj: 0x113df  csc: 0x00.95643  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1000080 ver: 0x01 opc: 0
     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000a.004.000010a3  0x00c028ab.00cb.04  --U-    2  fsc 0x0000.0009564a
0x02   0x000a.01b.0000109b  0x00c028aa.00cb.03  C---    0  scn 0x0000.0009558d
bdba: 0x01000086
data_block_dump,data header at 0x7f96a71b3264
===============
tsiz: 0x1f98
hsiz: 0x16
pbl: 0x7f96a71b3264
     76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f85
avsp=0x1f70
tosp=0x1f70
0xe:pti[0]      nrow=2  offs=0
0x12:pri[0]     offs=0x1f85
0x14:pri[1]     offs=0x1f8c
block_row_dump:
tab 0, row 0, @0x1f85
tl: 7 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 1]  31
col  1: [ 1]  61
tab 0, row 1, @0x1f8c
tl: 7 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 1]  32
col  1: [ 1]  62
end_of_block_dump

此时可以看到数据块中的列值发生了变化，原有的NULL更新为具体值。对于大表来说，这个操作将较为耗时。

案例四：继续增加带缺省值的非空列和不带缺省值的空列

在案例三的基础上继续

SQL> alter table test.t_obj add c3 varchar2(1) default 'c' not null;

Table altered.

SQL> select c1,c2,c3,dbms_rowid.rowid_object(rowid) obj#,
        dbms_rowid.rowid_relative_fno(rowid) rfile#,
        dbms_rowid.rowid_block_number(rowid) block#,
        dbms_rowid.rowid_row_number(rowid) row#                 
  from test.t_obj;  2    3    4    5  

C C C       OBJ#     RFILE# BLOCK#       ROW#
- - - ---------- ---------- ---------- ----------
1 a c      70623      4    134      0
2 b c      70623      4    134      1

SQL> select ec.* from sys.ecol$ ec,sys.obj$ o where ec.tabobj#=o.dataobj#;

   TABOBJ#     COLNUM BINARYDEFV
---------- ---------- ----------
     70623      3 63

SQL> alter system checkpoint;

System altered.

SQL> alter table test.t_obj add c4 varchar2(1);

Table altered.

SQL> insert into test.t_obj(c1,c4) values('3','3');

1 row created.

SQL> commit;

Commit complete.

SQL> select c1,c2,c3,c4,dbms_rowid.rowid_object(rowid) obj#,
        dbms_rowid.rowid_relative_fno(rowid) rfile#,
        dbms_rowid.rowid_block_number(rowid) block#,
        dbms_rowid.rowid_row_number(rowid) row#                 
  from test.t_obj;  2    3    4    5  

C C C C       OBJ#     RFILE#     BLOCK#       ROW#
- - - - ---------- ---------- ---------- ----------
1 a c        70623      4        134      0
2 b c        70623      4        134      1
3 b c 3      70623      4        134      2

Block header dump:  0x01000086
 Object id on Block? Y
 seg/obj: 0x113df  csc: 0x00.95643  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1000080 ver: 0x01 opc: 0
     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000a.004.000010a3  0x00c028ab.00cb.04  --U-    2  fsc 0x0000.0009564a
0x02   0x000a.01a.000010a8  0x00c028ac.00cb.1a  --U-    1  fsc 0x0000.000957eb
bdba: 0x01000086
data_block_dump,data header at 0x7f96a71b3264
===============
tsiz: 0x1f98
hsiz: 0x18
pbl: 0x7f96a71b3264
     76543210
flag=--------
ntab=1
nrow=3
frre=-1
fsbo=0x18
fseo=0x1f7a
avsp=0x1f63
tosp=0x1f63
0xe:pti[0]      nrow=3  offs=0
0x12:pri[0]     offs=0x1f85
0x14:pri[1]     offs=0x1f8c
0x16:pri[2]     offs=0x1f7a
block_row_dump:
tab 0, row 0, @0x1f85
tl: 7 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 1]  31
col  1: [ 1]  61
tab 0, row 1, @0x1f8c
tl: 7 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 1]  32
col  1: [ 1]  62
tab 0, row 2, @0x1f7a
tl: 11 fb: --H-FL-- lb: 0x2  cc: 4
col  0: [ 1]  33
col  1: [ 1]  62
col  2: [ 1]  63
col  3: [ 1]  33
end_of_block_dump

案例五：alter table move的影响

在案例四基础上继续：

SQL> alter table test.t_obj move;

Table altered.

SQL> alter system checkpoint;

System altered.

SQL> select c1,c2,c3,c4,dbms_rowid.rowid_object(rowid) obj#,
        dbms_rowid.rowid_relative_fno(rowid) rfile#,
        dbms_rowid.rowid_block_number(rowid) block#,
        dbms_rowid.rowid_row_number(rowid) row#                 
  from test.t_obj;  2    3    4    5  

C C C C       OBJ#     RFILE#     BLOCK#       ROW#
- - - - ---------- ---------- ---------- ----------
1 a c        70624      4        139      0
2 b c        70624      4        139      1
3 b c 3      70624      4        139      2

SQL> alter system dump datafile 4 block 139;

System altered.

Block header dump:  0x0100008b
 Object id on Block? Y
 seg/obj: 0x113e0  csc: 0x00.9594b  itc: 3  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1000088 ver: 0x01 opc: 0
     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000a.020.000010ad  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x03   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
bdba: 0x0100008b
data_block_dump,data header at 0x7f96a71b327c
===============
tsiz: 0x1f80
hsiz: 0x18
pbl: 0x7f96a71b327c
     76543210
flag=--------
ntab=1
nrow=3
frre=-1
fsbo=0x18
fseo=0x1f63
avsp=0x1f4b
tosp=0x1f4b
0xe:pti[0]      nrow=3  offs=0
0x12:pri[0]     offs=0x1f77
0x14:pri[1]     offs=0x1f6e
0x16:pri[2]     offs=0x1f63
block_row_dump:
tab 0, row 0, @0x1f77
tl: 9 fb: --H-FL-- lb: 0x0  cc: 3
col  0: [ 1]  31
col  1: [ 1]  61
col  2: [ 1]  63
tab 0, row 1, @0x1f6e
tl: 9 fb: --H-FL-- lb: 0x0  cc: 3
col  0: [ 1]  32
col  1: [ 1]  62
col  2: [ 1]  63
tab 0, row 2, @0x1f63
tl: 11 fb: --H-FL-- lb: 0x0  cc: 4
col  0: [ 1]  33
col  1: [ 1]  62
col  2: [ 1]  63
col  3: [ 1]  33
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 139 maxblk 139

结论

  1. _ADD_COL_OPTIM_ENABLED=true生效后，可以实现大表的快速新增带缺省值的非空列，对于生产环境较有意义。
  2. 使用该特性增加列，需注意后期一些操作的影响，例如：not null改为null、alter table move等。
  3. 该参数优化仅对新增带缺省值的非空列有效，对带缺省值的空列无效。

Oracle 12c又引入了_add_nullable_column_with_default_optim参数，可以对新增带缺省值的空列也有效。

©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 199,064评论 5赞 466
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 83,606评论 2赞 376
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 146,011评论 0赞 328
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 53,550评论 1赞 269
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 62,465评论 5赞 359
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 47,919评论 1赞 275
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 37,428评论 3赞 390
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,075评论 0赞 254
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 40,208评论 1赞 294
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,185评论 2赞 317
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,191评论 1赞 328
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 32,914评论 3赞 316
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 38,482评论 3赞 302
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,585评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 30,825评论 1赞 255
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 42,194评论 2赞 344
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 41,703评论 2赞 339