浅析Oracle 11g中对数据列默认值变化的优化

在日常的运维工作中，对生产数据表进行DDL操作是一件需要谨慎对待的事情。运维DBA们在进行数据DDL操作的时候，通常要全局考虑，诸如对生产影响、执行时间长度和影响存储数据等等。 数据列默认值的添加，是DBA们经常头疼的一个问题。传统的执行语句，消耗时间

在日常的运维工作中，对生产数据表进行DDL操作是一件需要谨慎对待的事情。运维DBA们在进行数据DDL操作的时候，通常要全局考虑，诸如对生产影响、执行时间长度和影响存储数据等等。

数据列默认值的添加，是DBA们经常头疼的一个问题。传统的执行语句，消耗时间长、资源使用量大，对生产环境影响程度高。采用其他的一些变通方法，又存在操作步骤繁琐的问题。如何快速的添加一个有默认值的数据列，同时对现有生产环境影响最小，是我们希望达到的一个目标。



本文从操作入手，探讨添加default数据列的问题点，最后介绍Oracle 11g中对其进行的“革命性”优化。

1、从10g的数据列添加谈起

为了实现对比效果，我们首选选择10g版本的Oracle进行试验，构造一个相对较大的数据表。


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 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> create table t as select object_id from dba_objects;
表已创建。

SQL> select count(*) from t;
COUNT(*)
----------
3220352


数据表t只包括一个数据列，但是数据量大约为320万条。我们从体积上进行评估如下：


SQL> set timing on;
SQL> select bytes/1024/1024,blocks from dba_segments where wner='SYS' and segment_name='T';

BYTES/1024/1024 BLOCKS
--------------- ----------
39 4992

已用时间: 00: 00: 00.03

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade=>true);
PL/SQL过程已成功完成。

已用时间: 00: 00: 00.35

SQL> select blocks from dba_tables where wner='SYS' and table_name='T';

BLOCKS
----------
4883

已用时间: 00: 00: 00.01


Oracle分配给这个段segment的中空间为4992个数据块，高水位线HWM下的格式化过数据块为4883。总体积约40M。

下面进行两种方式的添加数据表默认值列方法，一起观察一下变化情况。首先是允许为空默认值列的操作。


SQL> alter table t add vc varchar2(100) default 'TTTTTTTTTTTT';

表已更改。

已用时间: 00: 34: 37.15

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade=>true);

PL/SQL过程已成功完成。

已用时间: 00: 00: 03.86


SQL> select bytes/1024/1024,blocks from dba_segments where wner='SYS' and segment_name='T';

BYTES/1024/1024 BLOCKS
--------------- ----------
208 26624

已用时间: 00: 00: 00.06
SQL> select blocks from dba_tables where wner='SYS' and table_name='T';

BLOCKS
----------
25864

已用时间: 00: 00: 00.01


果然是一个费时的操作，添加一个数据列默认值，总共消耗了近30分钟时间。原有数据表的体积也发生的膨胀，从原来的不到40M，上升到了208M。

这个现象告诉我们，当我们添加一个有default值的数据列，并且是直接添加的时候，一些数据被插入到了数据块中，引起空间膨胀。

在原有的结构下，数据添加到数据块上是必需的，只有这样才能将数据列default添加到里面去。

除了这个字句，我们是还可以提供数据列的not null选项，也是可以实现相同的功能的。


SQL> alter table t add vc2 varchar2(100) default 'TTTTTTTTTTTT' not null;

表已更改。

已用时间: 00: 15: 58.85

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade=>true);

PL/SQL过程已成功完成。

已用时间: 00: 00: 36.87

SQL> select bytes/1024/1024,blocks from dba_segments where wner='SYS' and segme
nt_name='T';

BYTES/1024/1024 BLOCKS
--------------- ----------
256 32768

已用时间: 00: 00: 00.14
SQL> select blocks from dba_tables where wner='SYS' and table_name='T';

BLOCKS
----------
32448

已用时间: 00: 00: 00.04


也是消耗了15分钟，空间发生了很大程度变化。新空间分配，同时数据行数没有发生变化，潜在的行迁移（Row Migration）和行链接（Row Chaining）是严重恶化的！

综合分析Oracle 10g下的操作：为了添加上数据字段的默认值，Oracle会去访问每个数据块上的每个数据行进行数据列拓展工作，这个过程中还伴随着新空间分配和多余数据行复制。

这类型操作对于生产环境是恐怖的，在整个作业过程中，数据表结构被锁定，相关业务处理操作阻塞或者缓慢。所以，运维DBA都是选择在维护窗口或者变通的方法进行处理。

在Oracle 11g环境下，事情有了一些不同。

2、11g下的默认值配置

我们在11g上进行相似操作。


SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production


PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production


构建相似规模的数据表。


SQL> set timing on;
SQL> create table t as select object_id from dba_objects;
Table created

SQL> select count(*) from t;
COUNT(*)
----------
3323167

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);
PL/SQL procedure successfully completed

SQL> select segment_name, bytes/1024/1024, extents,blocks from user_segments where segment_name='T';

SEGMENT_NA BYTES/1024/1024 EXTENTS BLOCKS
---------- --------------- ---------- ----------
T 40 55 5120

SQL> select NUM_ROWS, BLOCKS from dba_tables where wner='SCOTT' and table_name='T';

NUM_ROWS BLOCKS
---------- ----------
3323167 5041


11g下我们准备了约330万数据，进行添加非空带默认值的数据列。


SQL> alter table t add vc2 varchar2(100) default 'TTTTTTTTTTTT' ;

alter table t add vc2 varchar2(100) default 'TTTTTTTTTTTT'

ORA-01013:用户请求取消当前的操作


在添加defalut列，不指定not null的时候，数据持续时间超过了我们的想象。笔者主动将其断开了。下面试试添加not null时候。

--1s不到完成操作；
SQL> alter table t add vc varchar2(100) default 'TTTTTTTTTTTT' not null;
Table altered

Executed in 0.047 seconds


SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);
PL/SQL procedure successfully completed

SQL> select NUM_ROWS, BLOCKS from dba_tables where wner='SCOTT' and table_name='T';
NUM_ROWS BLOCKS
---------- ----------
3323167 5041

Executed in 0 seconds

SQL> select segment_name, bytes/1024/1024, extents,blocks from user_segments where segment_name='T';

SEGMENT_NA BYTES/1024/1024 EXTENTS BLOCKS
---------- --------------- ---------- ----------
T 40 55 5120

SQL> select * from t where rownum

OBJECT_ID VC
---------- --------------------------------------------------------------------------------
20 TTTTTTTTTTTT
46 TTTTTTTTTTTT
28 TTTTTTTTTTTT
15 TTTTTTTTTTTT
（篇幅原因，有省略……）
9 rows selecte