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