Oracle11新特性——在线操作功能增强之表增加包含默认值的字段

在11g以前，当表很大的时候，如果往表中新增一个NOT NULL的字段，不光是执行速度慢，而且由于现有数据长度的变化，很容易造成表中大量的行链接情况，进而使表占用的空间大大增加。
在11g中，这种情况得到了彻底的改善，Oracle通过在数据字典中记录DEFAULT值，避免了繁重的更新操作，增加非空字段的时间和增加一个可空字段的时间完全一样。

10g中添加一个包含DEFAULT值的非空字段的测试：

 

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
Connected as ppsclient

SQL> select count(1) from yu_test1;

  COUNT(1)
----------
     31956

SQL>
SQL> Select owner, Segment_Name, Sum(bytes) / 1024 DSIZE
  2    From DBA_Extents
  3   where Segment_Name = 'YU_TEST1'
  4   Group By owner, Segment_Name
  5   order by 3 desc;

OWNER                          SEGMENT_NAME                                                                          DSIZE
------------------------------ -------------------------------------------------------------------------------- ----------
PPSCLIENT                      YU_TEST1                                                                               3072

SQL>

 

SQL> ALTER TABLE YU_TEST1 ADD NEW_COL CHAR(1000) DEFAULT 'TEST COLUMN' NOT NULL;

Table altered

耗时：4.12s

SQL>
SQL> Select owner, Segment_Name, Sum(bytes) / 1024 DSIZE
  2    From DBA_Extents
  3   where Segment_Name = 'YU_TEST1'
  4   Group By owner, Segment_Name
  5   order by 3 desc;

OWNER                          SEGMENT_NAME                                                                          DSIZE
------------------------------ -------------------------------------------------------------------------------- ----------
PPSCLIENT                      YU_TEST1                                                                              44032

 

 

11g中添加一个包含DEFAULT值的非空字段的测试：

 

 

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as SYS
 
SQL> select count(1) from yuzh.yu_test1;
 
  COUNT(1)
----------
     31956
 
SQL>
SQL> Select owner, Segment_Name, Sum(bytes) / 1024 DSIZE
  2    From DBA_Extents
  3   where Segment_Name = 'YU_TEST1'
  4   Group By owner, Segment_Name
  5   order by 3 desc;
 
OWNER                          SEGMENT_NAME                                                                          DSIZE
------------------------------ -------------------------------------------------------------------------------- ----------
YUZH                           YU_TEST1                                                                               3072
  
SQL>  ALTER TABLE yuzh.YU_TEST1 ADD NEW_COL CHAR(1000) DEFAULT 'TEST COLUMN' NOT NULL;
 
Table altered
 

耗时：0.89s

SQL>
SQL> Select owner, Segment_Name, Sum(bytes) / 1024 DSIZE
  2    From DBA_Extents
  3   where Segment_Name = 'YU_TEST1'
  4   Group By owner, Segment_Name
  5   order by 3 desc;
 
OWNER                          SEGMENT_NAME                                                                          DSIZE
------------------------------ -------------------------------------------------------------------------------- ----------
YUZH                           YU_TEST1                                                                               3072
 
SQL>

 

 

从上面的测试可以看出，虽然只有3万的数据，但是10g添加字段用了4秒，而11g只用了不到1s，而且不光是时间上快了很多，空间上夜节省了很多，10新增字段后差不多要44m，而11g增加后还是只有3m。再来的优点还有就是生成REDO和UNDO的大小，11g也远小于10g的，这方面的测试不做了。

这里又出现了个问题，为什么11g新增字段后表大小没有变化呢，说起来也很简单。Oracle11g中，在添加一个包含DEFAULT值的NOT NULL字段，Oracle不会去更新现有的数据，Oracle需要做的不过是将默认值以及对应的表信息、列信息一起存储在一个新增数据字典表ecol$中。这张表利用BLOB字段存储ALTER TABLE添加的DEFAULT值：

 

SQL> SELECT OBJECT_ID FROM DBA_OBJECTS WHERE OBJECT_NAME = 'YU_TEST1';
 
 OBJECT_ID
----------
     73202
 
SQL> SELECT * FROM SYS.ECOL$ WHERE TABOBJ# = 73202;
 
   TABOBJ#     COLNUM BINARYDEFVAL
---------- ---------- ------------
     73202          6 <BLOB>

 

Oracle在读取数据时，发现COLUMN 6列为非空，但是在存储的数据中找不到该列，于是就会从ECOL$中读取该列的默认值。
这个功能不但提高了添加非空字段的速度，而且节省了大量的磁盘空间。相比之下，在数据字典中查找DEFAULT VALUE的代价小的几乎可以忽略。