ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle11g新特性 - 快速在线新增not null字段

Oracle11g新特性 - 快速在线新增not null字段

原创 Linux操作系统 作者:shilei1 时间:2011-12-30 12:59:30 0 删除 编辑
问题:某电信系统业务,其中有一张表有上亿条信息记录,而且很重要;要求在不停库和不锁表的情况下;对该表增加一个新的字段,如何实现。
11g以前,在表中新增一个NOT NULL字段是件十分痛苦的事情,尤其是在表很大的情况,不但执行速度慢(会产生DDL锁,影响其它用户操作),而且由于数据长度的增加,很容易造成表中大量的行链接情况,影响性能。
在11g中,这种情况得到了彻底的改善Oracle通过在数据字典(ecol$)中记录DEFAULT值,避免了繁重的更新操作,增加非空字段的时间和增加一个可空字段的时间完全一样。
下面先看10g中的情况:
SQL> select * from v$version where rownum<2;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod

SQL> create table test as select * from dba_objects;

Table created.

SQL> select count(*) from test;

  COUNT(*)
----------
     24871
SQL> set timing on
SQL> alter table test add new_col varchar(1000)default'LARGE COLUMN'not null;

Table altered.

Elapsed: 00:00:20.34

可以看到即使对于只有2万多条数据的表,用了20秒,而且在这20秒期间,该表上有DDL锁,所有在该表的DML操作都会被阻塞。

注意,这里新增的列是非空的,如果没有非空这个限制,在10g中也是很快的:

SQL> alter table test add new_col2 varchar(1000);

Table altered.

Elapsed: 00:00:00.59

下面再看11g中的情况:
SQL> select * from v$version where rownum<2;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

SQL> create table test as select * from dba_objects;

Table created.

SQL> select count(*) from dba_objects;

  COUNT(*)
----------
     13617

SQL> set timing on
SQL> alter table test add new_col varchar(1000) default 'LARGE COLUMN' not null;

Table altered.

Elapsed: 00:00:01.34

可以看到在11g中只用了1秒,
那么Oracle是采用何种方法实现了如此大的性能提升呢?说起来也简单,在Oracle11g中,当添加一个包含DEFAULT值的NOT NULL字段,Oracle不会去更新现有的数据,仅仅是将默认值以及对应的表信息、列信息一起存储在一个新增数据字典表ecol$中。当Oracle在读取数据时,如果发现某一列为非空,但是实际存储却为空,就会从ECOL$中读取该列的默认值。

这样做的好处是不但减少了执行时间,而且不会产生DDL锁,进而不会影响其它用户的操作。

============

回到最开始的问题,如果数据库版本不是11g,那该怎么办呢,其实可以先新建一个允许空的字段,然后把该字段修改会非空,在对之前的数据进行update操作:
1. 新建一个允许空的字段。
SQL> alter table test add new_col varchar(1000);

Table altered.

Elapsed: 00:00:00.14
2. 修改该字段为非空,
该语句只对之后的记录起作用,原有记录的该列还是为空值,因此该操作也很快。
SQL> alter table test modify new_col varchar(1000) default 'LARGE COLUMN';

Table altered.

Elapsed: 00:00:00.48
3. 更新原有的字段为默认值。
SQL> alter session enable parallel dml;
SQL> update /*+ parallel(t 8) */ test set new_col='LARGE COLUMN' where new_col is null;

24871 rows updated.
SQL> alter session disable parallel dml

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/196700/viewspace-714275/,如需转载,请注明出处,否则将追究法律责任。

下一篇: Oracle rowid 详解
请登录后发表评论 登录
全部评论

注册时间:2018-10-10

  • 博文量
    534
  • 访问量
    8234