Oracle10g和11g在新增表字段时default的性能影响

 

Default属性的设置

一、        oracle 10g版本...1

1、版本...1

2、预置环境...1

3、测试：...2

测试一：...2

测试二：...2

测试三：...3

二、在oracle 11g版本下：...4

1、版本...4

2、预置环境...4

3、测试...5

测试一：...5

测试二：...5

测试三：...5

测试四：...6

测试五：...6

总结：...6

 

一、oracle 10g版本

1、版本

SQL> select * from v$version;

 

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi

PL/SQL Release 10.2.0.3.0 - Production

CORE        10.2.0.3.0         Production

TNS for Solaris: Version 10.2.0.3.0 - Production

NLSRTL Version 10.2.0.3.0 - Production

2、预置环境

SQL> drop table test_tab_10g;

 

Table dropped

SQL> create table test_tab_10g(id number primary key ,name varchar2(10));

 

Table created

SQL> insert into test_tab_10g values(1,'a');

 

1 row inserted

SQL> insert into test_tab_10g values(2,'b');

 

1 row inserted

SQL> insert into test_tab_10g values(3,'c');

 

1 row inserted

SQL> commit;

 

Commit complete

3、测试：

测试一：

SQL> alter table test_tab_10g add (d1 varchar2(2) not null);

 

alter table test_tab_10g add (d1 varchar2(2) not null)

 

ORA-01758: table must be empty to add mandatory (NOT NULL) column

 

SQL> alter table test_tab_10g add (d1 varchar2(2) );

 

Table altered

说明：向一个已有数据的表上新增一个非空字段，必须先保证该列数据无null值。

 

测试二：

SQL> alter table test_tab_10g modify d1 default '1';

 

Table altered

 

SQL> select * from test_tab_10g;

 

        ID NAME       D1

---------- ---------- --

         1 a         

         2 b         

         3 c         

SQL> insert into test_tab_10g(id,name) values(4,'d');

 

1 row inserted

 

SQL> commit;

 

Commit complete

 

SQL> select * from test_tab_10g;

 

        ID NAME       D1

---------- ---------- --

         1 a         

         2 b         

         3 c         

         4 d          1        

说明：向一个已有数据的表上，为一个已有列设置default(该列可以有数据，可以无数据)，不会将历史数据修正为default值，只会对新增数据形成影响。

 

测试三：

SQL> alter table test_tab_10g add(d2 varchar2(2) default '1');

 

Table altered

 

SQL> select * from test_tab_10g;

 

        ID NAME       D1 D2

---------- ---------- -- --

         1 a             1

         2 b             1

         3 c             1

         4 d          1  1

说明：向一个已有数据的表上，新增一个列，并且该列设置default（或者还新增not null属性），则会将历史数据修正为defalut值，并且对新增数据也形成影响。

因此我们对于此种ddl操作，如果该表还包含较大量数据，并且应用对锁表时间有限制，则必须考虑采用以下方法：

alter table test_tab_10g add(d2 varchar2(2));

alter table test_tab_10g modify d2 default '1';

update test_tab_10g  set d2='1' where d2 is not null and ...; --考虑分段提交和断点续作的方法。

如果该字段的属性还需要not null，则需在update为非空值后，再进行alter table test_tab_10g modify d2 not null;

如果新增字段的属性为default+not null，则应该：

（1）新增字段：

  alter table test_tab_10g add(d2 varchar2(2));

（2）update历史数据为某非空值，或者default值:

  update test_tab_10g  set d2='1' where d2 is not null and ...

（3）调整该字段为not null属性，并新增default属性：

  alter table test_tab_10g modify d2 default '1' not null;

 

二、在oracle 11g版本下：

 1、版本

SQL> select * from v$version;

 

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

PL/SQL Release 11.2.0.3.0 - Production

CORE        11.2.0.3.0         Production

TNS for Solaris: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

2、预置环境

SQL> create table test_tab_11g(id number primary key,name varchar2(10));

Table created

SQL> begin

  2  for i in 1..50000 loop

  3  insert into test_tab_11g values(i,'a'||i);

  4  end loop;

  5  commit;

  6  end;

  7  /

 

 

PL/SQL procedure successfully completed

 

Executed in 4.742 seconds

3、测试

测试一：

SQL> set timing on

SQL> alter table test_tab_11g add (d1 varchar2(2) not null);

 

alter table test_tab_11g add (d1 varchar2(2) not null)

 

ORA-01758: 要添加必需的 (NOT NULL)列, 则表必须为空

说明：如果要新增一个非空列，需要保证该列数据都为not null

 测试二：

SQL> alter table test_tab_11g add (d1 varchar2(2));

 

Table altered

 

Executed in 0.11 seconds

 

SQL> alter table test_tab_11g modify d1 default '1';

 

Table altered

 

Executed in 0.109 seconds

 

SQL>  select * from test_tab_11g where rownum<3;

 

        ID NAME       D1

---------- ---------- --

      1057 a1057     

      1058 a1058     

说明：在oracle11g版本下向一个已有列增加default属性，则不修正历史数据，只对新增数据形成影响。

测试三：

SQL> alter table test_tab_11g add(d2 varchar2(2) default '1');

 

Table altered

 

Executed in 6.038 seconds

SQL> select * from test_tab_11g where rownum<3;

 

        ID NAME       D1 D2

---------- ---------- -- --

      1057 a1057         1

      1058 a1058         1

 

Executed in 0.202 seconds

说明：在11g版本下新增带有default属性的列（可控），执行时间超过单纯增加列的时间，因为需要对历史数据进行修正。

测试四：

SQL> alter table test_tab_11g add(d3 varchar2(2) default '1' not null);

 

Table altered

 

Executed in 0.468 seconds

说明：在11g版本下新增带有default+not null属性的列，如果default是常量默认值，则执行时间很短。

测试五：

SQL> alter table test_tab_11g add(d5 varchar2(32) default sys_guid() not null);

 

Table altered

 

Executed in 9.344 seconds

说明:在11g版本下新增带有default属性+not null的列，如果default是函数或表达式，则执行时间也会较长。

 

总结：

在修改表时，如果增加有default值的列或者修改字段的非空属性，必须先做ddl增加字段，再做ddl修改增加字段的default属性，然后再做dml更新值，最后加非空属性。但如果所修改数据库是Oracle 11g版本，且增加非空字段并赋予常量默认值，则无需此操作；如果增加可空字段或赋予的default值为函数或表达式，例如sys_guid、sysdate，则仍需参照上述步骤实施。