ITPub博客

首页 > 数据库 > Oracle > 表列添加默认值的方法

表列添加默认值的方法

原创 Oracle 作者:to_be_dba 时间:2015-10-23 14:04:58 0 删除 编辑
在修改表结构时,有时候会涉及到添加默认值。
下面是在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_result add 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<to_date('20150501','yyyymmdd')
  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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-11-23

  • 博文量
    148
  • 访问量
    391921