在项目开发中,给已有的表增加字段并设置默认值还是比较常见的,但很多同事对此关注与重视得不够,因此引发了一些问题,所以对此进行补充说明,同时也介绍一下规范的调整:
首先,原来的规范是:
至所以把一个SQL要分解成三段,是因为oracle在设置默认值时,会直接修改每一条记录,直到全部完成,这个语句才算完,对于大表,这有可能带来长时间的锁表,引发各种问题;
不过Oracle在11G之后(顺便说明一下,shunyin的数据库版本目前都是11.2.0.3),对这个地方进行了一个优化,如果这个字段是“not null”的话,它就不再像以前那样去更新全部记录了,而是把默认值保存到数据字典中,等到查询时再通过nvl(null,默认值)的手法去转换了。
这给我们带来的利好是:如果现在我们要给一个大表加字段,并设默认值,并且这个字段是“not null”, 那就不需要分成三段来整了,
案例如下,只用两秒就完成了在一个8千万的大表增加字段并赋默认值的操作:
但是,请注意SQL最后的“not null”子句,如果不带上的话,那就还会像原来一样,每一条都得更新,那就要跑很久很久了:
所以,如果这个字段是“null”,那么请仍然按原规范执行(分三段,分批提交),不过如果更新记录在1万条以下,可以考虑放宽;
现在体会到“not null”的好处了吧,顺便吐槽一下,not null在SQL优化中也有作用,但很多表的设计,还是不太注意这个地方;
不过,有些场合,字段就得是null,或者有时候就是要更新大量的记录,这个时候应该怎么做呢?有哪些问题是要特别注意的呢?
目前,大多数同事习惯的都是直接一个UPDATE语句,类似如下:
UPDATE OFFLINE_BUSINESS SET VCODE_STATUS='NORMAL'
这种方式的好处是代码量少,简单,容易掌握,在小数据量下,速度也是最快的(比起pl/sql);
但如果表一大,问题就多了:首先是你没法知道执行了哪里了,然后发现资源消耗原来越大:CPU、内存或者IO占到满、回滚段爆涨,日志归档不过来、其他用户操作极慢或无法登录,在压力之下最后只好rollback;就算oracle撑过来了,不担保其他配套系统不受影响,有发生过大量更新导致GG占用大量内存导致节点重启的,所以数据量一大,就强烈不建议直接更新了;
那什么样的数据量才算大呢?很难把这个数字确定得非常精确,但如果修改的记录数超过100万,则强烈不建议采用直接UPDATE方式;
如果采用分批提交的方式,一个困难是很多人不熟悉相应的代码,这里给出一个供参考的例子,1000条提交一次:
DECLARE
CURSOR cur IS
SELECT ob.rowid ROW_ID
FROM dm_ord.offline_business ob
ORDER BY ob.ROWID;
V_COUNTER NUMBER;
BEGIN
V_COUNTER := 0;
FOR row IN cur LOOP
UPDATE OFFLINE_BUSINESS SET VCODE_STATUS='NORMAL' WHERE ROWID = row.ROW_ID;
V_COUNTER := V_COUNTER + 1;
IF (V_COUNTER >= 1000) THEN
COMMIT;
V_COUNTER := 0;
END IF;
END LOOP;
COMMIT;
END;
还有一种增加了批量绑定的例子:
declare
type rowid_list is table of urowid index by binary_integer;
rowid_infos rowid_list;
i number;
cursor c_rowids is select rowid from dm_ord.offline_business ob;
begin
open c_rowids;
loop
fetch c_rowids bulk collect
into rowid_infos limit 2000;
forall i in 1 .. rowid_infos.count
UPDATE OFFLINE_BUSINESS
SET VCODE_STATUS = 'NORMAL'
where rowid = rowid_infos(i);
commit;
exit when rowid_infos.count < 2000;
end loop;
close c_rowids;
end;
这两种写法差别不算太大,都可以,好处是点用资源固定,稳定,保险;
Oracle在11GR2之后,又推出了一种并行任务的方式,代码如下:
declare
vc_sql varchar2(1000);
n_try number;
n_status number;
begin
--create a task
dbms_parallel_execute.create_task(task_name => 'Huge_Update');
--By Rowid
dbms_parallel_execute.create_chunks_by_rowid(task_name => 'Huge_Update',
table_owner => 'DM_ORD',
table_name => 'OFFLINE_BUSINESS',
by_row => true,
chunk_size => 10000);
vc_sql := 'update /*+rowid(ob)*/ OFFLINE_BUSINESS ob SET VCODE_STATUS = ''NORMAL'' where rowid between :start_id and :end_id';
dbms_parallel_execute.run_task(task_name => 'Huge_Update',
sql_stmt => vc_sql,
language_flag => dbms_sql.native,
parallel_level => 8);
--防止失败后重启
n_try := 0;
n_status := dbms_parallel_execute.task_status('Huge_Update');
while (n_try < 2 and (n_status != dbms_parallel_execute.FINISHED)) loop
n_try := n_try + 1;
dbms_parallel_execute.resume_task('Huge_Update');
n_status := dbms_parallel_execute.task_status('Huge_Update');
end loop;
dbms_output.put_line('' || n_try);
dbms_parallel_execute.drop_task('Huge_Update');
end;
这种方式不但实现了分批处理,还能利用并行,充分利用资源,又不会有相关问题,是我比较推荐的方式;
根据测试,直接UPDATE offline_business全表8636万在运行70分钟之后被手工停止,期间还造成了环境不能登录,但这种方式在60分钟成功完成;
以上,供参考,若有不足之处,也请指正,谢谢!
联系客服