生产过程中常常会遇到对表需要在线增加列,尤其是新增带缺省值的非空列,例如:
ALTER TABLE .. ADD COLUMN ( ... NOT NULL DEFAULT ... )
在Oracle 11g以前,执行这条DDL会涉及以下步骤:
在数据字典中中增加新列定义:列类型、长度、NOT NULL及DEFAULT等;
更新表上所有行,使用新列的缺省值增加新列的值。
对百万级以上的大表,该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)