|
来自银醇超
在项目开发中，给已有的表增加字段并设置默认值还是比较常见的，但很多同事对此关注与重视得不够，因此引发了一些问题，所以对此进行补充说明，同时也介绍一下规范的调整： 首先，原来的规范是： 至所以把一个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分钟成功完成； 以上，供参考，若有不足之处，也请指正，谢谢！ |
|
|
