C/C++教程

Oracle 11g大数据量表快速增加列

本文主要是介绍Oracle 11g大数据量表快速增加列,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

GPS平台、网站建设、软件开发、系统运维,找森大网络科技!
https://cnsendnet.taobao.com
来自森大科技官方博客
http://www.cnsendblog.com/index.php/?p=1975

 

Oracle 11g增加列,并带默认值的新特性

网址: http://www.eygle.com/digest/2011/06/oracle_11g_default_column.html

永久链接:http://www.ixdba.com/html/y2007/m08/159-oracle11g-add-column.html


老和尚在他的blog中描述了了这个问题,我这里做一个详细的测试来说明这个问题。

在oracle 11g以前,如果需要在一个表中执行类似如下的命令,而且这个表本身已经有很多数据,那么,这个操作将可能花费很长的时间,并且可能阻塞应用:

  • SQL>alter table table_name add field_name number default 0 not null;

因为这个操作需要修改以前所有的行,并把他们都修 改为默认值,如以上的0。但是,这个情况在oracle 11g中有了巨大的改变,oracle 11g中,如果对一个表增加一个列,并带有默认值,Oracle并没有真实的去修改以前的列,只不过通过查询的时候,采用类似NVL(null,新值)的 方法转换一下,让以前的列看起来象有值一样。

这个过程在select 的时候就转换好了,所以,对于使用者看来,他们是有值的,这个细小的改动将大大减少这个语句的执行时间,使得这样的语句在oracle 11g中不会引起任何阻塞,更不会影响性能了。

下面看一个简单的对比说明:

首先是Oracle 10g的测试

  • Piner@10gR2>create table test(a int); 
  • Table created.
  •  
  • Piner@10gR2>begin
  •   2    for i in 1..1000 loop
  •   3     insert into test values('1');
  •   4    end loop;
  •   5  commit;
  •   6  end;
  •   7  /
  • PL/SQL procedure successfully completed.
  •  
  • Piner@10gR2>set serveroutput on
  • Piner@10gR2>exec show_space('TEST');
  • Total Blocks............................8
  • Total Bytes.............................65536
  • Unused Blocks...........................0
  • Unused Bytes............................0
  • Last Used Ext FileId....................4
  • Last Used Ext BlockId...................135641
  • Last Used Block.........................8
  • PL/SQL procedure successfully completed.
  •  
  •  
  • Piner@10gR2>alter table test add flag char(2000) default 1 not null;   
  • Table altered.
  •  
  • Piner@10gR2>exec show_space('TEST');
  • Total Blocks............................384
  • Total Bytes.............................3145728
  • Unused Blocks...........................0
  • Unused Bytes............................0
  • Last Used Ext FileId....................4
  • Last Used Ext BlockId...................134409
  • Last Used Block.........................128
  • PL/SQL procedure successfully completed.
  •  
  • Piner@10gR2>select count(*) from test where flag is null;
  •  
  •   COUNT(*)
  • ----------
  •          0

可以看到的是,在增加默认值的列以后,该表的使用空间发生了巨大的变化,也证明数据库修改了以前的所有的块中的数据,让他们都生效。

以下是Oracle 11g的操作

  • Piner@11gR1>create table test(a int); 
  • Table created.
  •  
  • Piner@11gR1>begin
  •   2    for i in 1..1000 loop
  •   3     insert into test values('1');
  •   4    end loop;
  •   5  commit;
  •   6  end;
  •   7  /
  • PL/SQL procedure successfully completed.
  •  
  • Piner@11gR1>set serveroutput on
  • Piner@11gR1>exec show_space('TEST');
  • Total Blocks............................8
  • Total Bytes.............................65536
  • Unused Blocks...........................0
  • Unused Bytes............................0
  • Last Used Ext FileId....................4
  • Last Used Ext BlockId...................83745
  • Last Used Block.........................8
  • PL/SQL procedure successfully completed.
  •  
  • Piner@11gR1>alter table test add flag char(2000) default 1 not null;
  • Table altered.
  •  
  • Piner@11gR1>exec show_space('TEST');
  • Total Blocks............................8
  • Total Bytes.............................65536
  • Unused Blocks...........................0
  • Unused Bytes............................0
  • Last Used Ext FileId....................4
  • Last Used Ext BlockId...................83745
  • Last Used Block.........................8
  • PL/SQL procedure successfully completed.
  •  
  • Piner@11gR1>select count(*) from test where flag is null;
  •  
  •   COUNT(*)
  • ----------
  •          0

这里可以看到,同样的记录,做了同样的操作,增加新列,并设置默认值以后,表的使用空间没有发生任何变化,也同样证明了Oracle没有修改以前的块中的数据。

这里的例子只有1000条记录,主要是为了体现了 空间的变化。如果是几是万条,或者几百万条以上的表,同样的操作,oracle 11g是马上就结束了,不会有任何影响,而oracle 10g可能需要非常长的时间,这个时间表是被锁住的,不能执行任何DML操作,对于写频繁的应用来说,将会导致应用的阻塞。

 

新添加的列,如果指定了默认值,并没有修改以前的块,读取以前的行的时候,可能发生一个转换过程。但是,如果对于已经设置过默认值的情况,插入新值的时候,这么默认值是怎么处理的呢:

之前的情况:

  • Piner@11gR1> desc test;
  • Name Type       Nullable Default Comments
  • ---- ---------- -------- ------- --------
  • A    INTEGER    Y                         
  • FLAG CHAR(2000) Y        0               
  •  
  • Piner@11gR1>exec show_space('TEST');
  • Total Blocks............................8
  • Total Bytes.............................65536
  • Unused Blocks...........................0
  • Unused Bytes............................0
  • Last Used Ext FileId....................4
  • Last Used Ext BlockId...................83745
  • Last Used Block.........................8
  • PL/SQL procedure successfully completed.
  •  
  • Piner@11gR1>select count(*) from test;
  •   COUNT(*)
  • ----------
  •       1000

插入新记录:

  • Piner@11gR1>begin
  •   2       for i in 1..1000 loop
  •   3         insert into test(a) values('1');
  •   4       end loop;
  •   5     commit;
  •   6  end;
  •   7  /
  • PL/SQL procedure successfully completed.
  •  
  • Piner@11gR1>exec show_space('TEST');
  • Total Blocks............................384
  • Total Bytes.............................3145728
  • Unused Blocks...........................0
  • Unused Bytes............................0
  • Last Used Ext FileId....................4
  • Last Used Ext BlockId...................90889
  • Last Used Block.........................128
  • PL/SQL procedure successfully completed.

可以看到,新插入的1000条记录,块的个数发生了非常大的变化,证明了在新的记录中,Oracle实际上是真正把默认值添加到块中去了。但是,只有以前的记录依然是没有值的,需要靠转换来完成。

现在有2000条记录,是分2次插入进去的,这个时候,如果去掉默认值,会发生什么情况?是一半有值,一半没有值,还是都有值呢?我们再看:

  • Piner@11gR1>alter table test modify flag default null null;
  • Table altered.
  •  
  • Piner@11gR1>select count(*) from test where flag is null;
  •   COUNT(*)
  • ----------
  •          0
  •  
  • Piner@11gR1>exec show_space('TEST');
  • Total Blocks............................384
  • Total Bytes.............................3145728
  • Unused Blocks...........................0
  • Unused Bytes............................0
  • Last Used Ext FileId....................4
  • Last Used Ext BlockId...................90889
  • Last Used Block.........................128
  • PL/SQL procedure successfully completed.

可以看到,虽然解除了默认值,块的个数也没有任何变化,以前的记录依然是没有真实的写入任何数值到块中的,但是,Oracle 依然能把以前的默认值显示出来,因为is null查不出来任何记录,而且查询记录就可以看到实际的值就是以前的默认值。

最后,再增加1000条记录,这个时候可以发现,因为去掉了默认值,现在在另外一个列上,写入的为null。

  • Piner@11gR1>begin
  •   2       for i in 1..1000 loop
  •   3         insert into test(a) values(1);
  •   4       end loop;
  •   5     commit;
  •   6  end;
  •   7  /
  • PL/SQL procedure successfully completed.
  •  
  • Piner@11gR1>exec show_space('TEST');
  • Total Blocks............................384
  • Total Bytes.............................3145728
  • Unused Blocks...........................0
  • Unused Bytes............................0
  • Last Used Ext FileId....................4
  • Last Used Ext BlockId...................90889
  • Last Used Block.........................128
  • PL/SQL procedure successfully completed.
  •  
  • Piner@11gR1>select count(*) from test where flag is null;
  •   COUNT(*)
  • ----------
  •       1000

所以,对于整个过程分析下来,可以这么认 为,oracle在add column default的时候,只是记录了一个标记,标记为这个点之前的所有块,如果是NULL的话,是需要转换的,反之取出实际的值。而这个点之后的块,就是按 照实际的值来处理,如果中途反复修改,则以修改为准。

因为add column default,对于一个列,最多发生一次,所以,一个列只记录一个参照点即可。

但是,对于这个特性,有人想问,如果想保持以前的值为null,不要强行转换,但是,又想把新增加的列设置一个默认值,怎么办?其实也很简单,跟以前一样,分2步走即可,这样的话,以前的值还是null,新的值将为默认值。

  • Piner@11gR1>alter table table_name add field_name number;
  • Piner@11gR1>alter table table_name modify field_name default 0;

 

 

 

-- Add/modify columns  数据量大的表新增字段,需要加上not null,否则会很慢  edit by huch

alter table OBD_DATA add errorcode varchar2(20) default '' not null;

-- Add comments to the columns

comment on column OBD_DATA.errorcode

  is '错误代码';

可以先这是为不可为空,后面再改成可为空,在湖北正式试了,执行后面那两条sql语句也非常快 

 

 

GPS平台、网站建设、软件开发、系统运维,找森大网络科技!
https://cnsendnet.taobao.com
来自森大科技官方博客
http://www.cnsendblog.com/index.php/?p=1975

这篇关于Oracle 11g大数据量表快速增加列的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!