在修改表结构时,有时候会涉及到添加默认值。
下面是在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/,如需转载,请注明出处,否则将追究法律责任。