oracle创建列默认值,表列添加默认值的方法

在修改表结构时，有时候会涉及到添加默认值。

下面是在11.2.0.3版本数据库中进行测试的结果：

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0

Connected as borpt

SQL> set timing on

SQL> select count(1) from user_mark_resultt;

COUNT(1)

----------

1451535

Executed in 0.219 seconds

当前数据量为145万

操作方法1：先增加表列，再修改默认值

SQL> alter table user_mark_resultadd name2 varchar2(10) ;

Table altered

Executed in 0.047 seconds

SQL> alter table user_mark_result modify name2 default 'a';

Table altered

Executed in 0.063 seconds

操作方法2：直接增加表列并赋予默认值

SQL> alter table user_mark_result add name3 varchar2(10) default 'a' ;

Table altered

Executed in 45.86 seconds

结果如下：

SQL> select t.dealerid,t.name2,t.name3 from user_mark_result t where rownum<5;

DEALERID                       NAME2      NAME3

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

DL0120100619                              a

DL0120100621                              a

DL0120100628                              a

DL0120100904                              a

Executed in 0.062 seconds

通过以上比较可知：

1.为表增加字段后，再修改默认值，已存在的数据不受影响

2.增加字段语句中同时为字段添加默认值，需要较长时间的锁表，这在生产系统上通常是不可接受的

因此，在oracle中进行表的添加列默认值操作时，为减少锁表时间，需要分三步操作：

1. 向表中添加字段带有default值时，做如下修改

alter table user_mark_resultadd name2 varchar2(10);

2.在字段添加完成后，分多个小事务更新新加的字段值，避免锁表。可以创建错误日志记录表，记录可能发生的错误：

create table err_log(status varchar2(200));

declare

n1 number :=0;

v_str varchar2(200);

begin

for i in (select dealerid from user_mark_result)

loop

n1 := n1+1;

update user_mark_result  set name2 = 'a' where dealerid = i.dealerid;

if mod(n1,5000)=0 then

commit;

end if;

end loop;

commit;

exception

when others then

rollback;

v_str :=  SQLCODE || '_' || SQLERRM;

insert into err_log (status) values(v_str);

commit;

end;

/

3.如果上述匿名块正常执行，则删除err_log表，完成操作；否则根据err_log中的错误记录进行相应修改。

4.增加表列的默认值

alter table user_mark_result modify name2 default 'a';

另外，上面的更新方法步骤2需要在每天更新语句后进行判断，如果使用游标处理，每次更新5000条数据，则效率更高。

以下是用order_table测试的结果：

SQL> select count(1),to_char(e.order_time,'yyyy-mm-dd')

2  from order_table e

3  where e.order_time>=to_date('20150401','yyyymmdd')

4  and  e.order_time

5  group by to_char(e.order_time,'yyyy-mm-dd')

6  order by to_char(e.order_time,'yyyy-mm-dd');

COUNT(1) TO_CHAR(E.ORDER_TIME,'YYYY-MM-

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

1438047 2015-04-01

1312835 2015-04-02

1167460 2015-04-03

921161 2015-04-04

802476 2015-04-05

750283 2015-04-06

682537 2015-04-07

651092 2015-04-08

629104 2015-04-09

667710 2015-04-10

648531 2015-04-11

658504 2015-04-12

593864 2015-04-13

576714 2015-04-14

589528 2015-04-15

612004 2015-04-16

636615 2015-04-17

631522 2015-04-18

673595 2015-04-19

649317 2015-04-20

645253 2015-04-21

635374 2015-04-22

668124 2015-04-23

658031 2015-04-24

666737 2015-04-25

729222 2015-04-26

736643 2015-04-27

740636 2015-04-28

739578 2015-04-29

1013999 2015-04-30

30 rows selected

通过以上查询，4月5日~12日的数据量为4831733条，

4月22日~28日的数据量为4834767条，基本相同，作为测试数据。

1)创建错误记录表：

SQL> drop table err_log;

Table dropped

Executed in 0.063 seconds

SQL> create table err_log(status varchar2(200));

Table created

Executed in 0.032 seconds

SQL> select * from err_log;

STATUS

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

Executed in 0.016 seconds

2)使用游标的方法进行数据更新：

SQL> DECLARE

2    TYPE typ_order_id IS TABLE OF order_table.order_id%type;

3    order_id     typ_order_id;

4    v_str varchar2(200);

5    cursor cur_order is  --声明游标

6      SELECT order_id

7        FROM order_table e

8       where e.order_time > to_date('20150405', 'yyyymmdd')

9         and e.order_time < to_date('20150412', 'yyyymmdd');

10  BEGIN

11    open cur_order; --打开游标

12    loop

13      fetch cur_order BULK COLLECT  --将数据插入数组，每5000条批量插入一次

14        INTO order_id limit 5000;

15      forall i in order_id.first .. order_id.last  --对这5000条数据进行批量更新

16        update order_table e

17           set e.new_price = 0

18         where e.order_id = order_id(i);

19      commit;

20      exit when cur_order%notfound;

21    end loop;

22    close cur_order;  --循环结束后，关闭游标

23  exception

24    when others then

25      rollback;

26      v_str :=  SQLCODE || '_' || SQLERRM;

27      insert into err_log (status) values(v_str);

28      commit;

29  END;

30  /

PL/SQL procedure successfully completed

Executed in 738.281 seconds

使用逐条更新，每5000条提交一次的方法：

SQL> declare

2    n1 number :=0;

3    v_str varchar2(200);

4  begin

5    for i in (SELECT order_id

6        FROM order_table e

7       where e.order_time > to_date('20150422', 'yyyymmdd')

8         and e.order_time < to_date('20150429', 'yyyymmdd') )

9     loop

10      n1 := n1+1;

11      update order_table  set new_price = 0 where order_id = i.order_id;

12      if mod(n1,5000)=0 then

13        commit;

14      end if;

15     end loop;

16     commit;

17  exception

18    when others then

19      rollback;

20      v_str :=  SQLCODE || '_' || SQLERRM;

21      insert into err_log (status) values(v_str);

22      commit;

23  end;

24  /

PL/SQL procedure successfully completed

Executed in 901.36 seconds

SQL> select * from err_log;

STATUS

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

Executed in 0.016 seconds

SQL> drop table err_log;

Table dropped

Executed in 0.094 seconds

使用游标的方法比逐条插入的方法快了约160s，性能差距还是比较明显的。

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