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