查看: 10959|回复: 41

[PL/SQL] 【大话IT】分批提交、并发处理、减少日志

[复制链接]
论坛徽章:
1
优秀写手
日期:2014-10-24 06:00:14
发表于 2014-11-27 19:06 | 显示全部楼层 |阅读模式
我的问题的核心是:分批提交、并发处理、减少日志三点的结合;

代码如下,我将t_user表中的数据全量插入到t_user_all表中,由于表数据量非常大,打算分批提交,实现方式如下,但是执行时系统报“无法在并行模式下修改之后读/修改”错误;
备注1:测试时t_user表需要有2条以上的测试数据,1条数据或空表不会报错的;
备注2:需要将目标表t_user_all表置为nologging模式且在insert后面加上/*+ append*/,网上有说将/*+ append*/修改为/*+ nologging*/,这样的话好像不能减少日志记录
备注3:/*+ append*/结合nologging会锁表,有没有更好的降低日志量的方式
备注4:插入的目标表为分区表的话,多分区同时插入,加上/*+ append*/估计同样也会因锁表而无法多分区并发插入;

下面的存储过程把forall修改为普通的for循环也可以解决,但是我其实是看中的是forall的本身的优化,即减少sql引擎和pl/sql引擎之间的上下文切换所浪费的开销,forall是批量处理完才切换上下文的,所以如果能用forall是最好的;

背景:系统改造,老系统数据迁移进新系统,许多数据需要整合后插入新的系统表,这里面会涉及到一些dml,数据量比较大,中间运行可能会出错或有脏数据,需要分批次提交,成功的下次就不用再处理了。希望能减少日志从而提高迁移效率。
下面的insert语句只是举的例子,实际的sql语句要复杂许多。
简单介绍一下系统的处理流程大致是:分行供数、迁移库加载入库、数据检核清理、数据转换到迁移库的目标表、数据卸载同步到生产库新系统;

分行供数的表现在设计成分区表,多个分行同时供数的话流程会并发处理同一张分区表;
迁移流程要支持错误数据不影响迁移流程,正确数据先处理,所以要分批处理并修改状态;

请问如何修改能达到分批提交且尽量少写日志,或有什么其他好的实现方式?


declare
  TYPE v_CursorVar IS REF CURSOR; --定义一个游标变量
  Cursor_backup v_CursorVar;
  TYPE rowid_list IS VARRAY(2000) OF varchar2(30);
  v_rowid_list rowid_list;
  v_sql_insert varchar2(200);
  v_sql_rowid  varchar2(200);
begin
  v_sql_insert := 'insert /*+ append*/ into t_user_all partition(p_110) t1 select t2.* from t_user partition(p_110) t2 where rowid = :1';
  v_sql_rowid  := 'select rowid row_id from t_user partition(p_110) order by rowid';

  --通过rowid采用批量提交的方式
  open Cursor_backup for v_sql_rowid;
  loop
       fetch Cursor_backup bulk collect into v_rowid_list limit 2000;
       exit when v_rowid_list.count = 0;
       forall i in 1 .. v_rowid_list.count
       execute immediate v_sql_insert using v_rowid_list(i);
       commit;
  end loop;

  close Cursor_backup;
end;

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

感谢newkid用户的帮助,目前已解决分批次提交+nologging的部分,代码如下:
declare
  TYPE v_CursorVar IS REF CURSOR; --定义一个游标变量
  Cursor_backup v_CursorVar;
  v_rowid SYS.ODCIVARCHAR2LIST;
  v_sql_insert varchar2(200);
  v_sql_rowid  varchar2(200);

begin
  v_sql_insert := 'insert /*+ append*/ into t_user_all t1 select t2.* from t_user t2 where exists(SELECT null FROM TABLE(:1) t3 where t2.rowid = t3.column_value)';
  v_sql_rowid  := 'select rowid row_id from t_user order by rowid';

  --通过rowid采用批量提交的方式
  open Cursor_backup for v_sql_rowid;
  loop
       fetch Cursor_backup bulk collect into v_rowid limit 2000;
       exit when v_rowid.count = 0;
       execute immediate v_sql_insert using v_rowid;
       commit;
  end loop;

  close Cursor_backup;
end;


论坛徽章:
737
季节之章:春
日期:2015-07-31 17:16:29ITPUB季度 技术新星
日期:2014-07-17 14:37:00季节之章:秋
日期:2015-07-31 17:16:14季节之章:夏
日期:2015-07-31 17:16:29股神
日期:2014-10-15 09:23:31衰神
日期:2014-10-20 22:47:12季节之章:冬
日期:2015-07-31 17:16:14红钻
日期:2014-12-16 17:51:41洛杉矶湖人
日期:2016-09-23 08:18:15布鲁克林篮网
日期:2016-09-23 08:17:18
发表于 2014-11-27 20:36 来自手机 | 显示全部楼层
既然有分区,可以并行插入不同的分区

使用道具 举报

回复
论坛徽章:
1
优秀写手
日期:2014-10-24 06:00:14
 楼主| 发表于 2014-11-27 22:41 | 显示全部楼层
oracle_cj 发表于 2014-11-27 20:36
既然有分区,可以并行插入不同的分区

但是每个分区的记录数还是比较多,估计单个分区多的会有千万级的数据,我想要的是两点,一:分批次提交;二:少写日志;

使用道具 举报

回复
论坛徽章:
520
奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:532009日食纪念
日期:2009-07-22 09:30:00
发表于 2014-11-27 22:47 | 显示全部楼层
用CREATE TABLE ... AS SELECT 一个分区;
把这个新创建的表用分区交换置入到备份表中。

使用道具 举报

回复
论坛徽章:
1
优秀写手
日期:2014-10-24 06:00:14
 楼主| 发表于 2014-11-27 23:01 | 显示全部楼层
newkid 发表于 2014-11-27 22:47
用CREATE TABLE ... AS SELECT 一个分区;
把这个新创建的表用分区交换置入到备份表中。

如果是简单的备份分区表中一个分区是可以使用你提供的方法,不过上面的insert的我只是举得一个例子,实际的sql要复杂许多,而且很多地方会涉及到需要分批次提交,所以我想找到一种能分批次提交且尽量少的写日志的方法。

使用道具 举报

回复
论坛徽章:
1
优秀写手
日期:2014-10-24 06:00:14
 楼主| 发表于 2014-11-27 23:06 | 显示全部楼层
donglin243870 发表于 2014-11-27 23:01
如果是简单的备份分区表中一个分区是可以使用你提供的方法,不过上面的insert的我只是举得一个例子,实际 ...

是系统改造,老系统数据迁移进新系统,许多数据需要整合后插入,这里面会涉及到一些dml,数据量比较大,中间运行可能会出错或有脏数据,需要分批次提交,成功的下次就不用再处理了。希望能减少日志从而提高迁移效率。

使用道具 举报

回复
论坛徽章:
520
奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:532009日食纪念
日期:2009-07-22 09:30:00
发表于 2014-11-27 23:28 | 显示全部楼层
看起来是FORALL INSERT不支持APPEND方式。
你可以改用 INSERT /*+ APPEND */ ... SELECT FROM ...TABLE(嵌套表)的方式看看。

使用道具 举报

回复
论坛徽章:
0
发表于 2014-11-28 11:00 | 显示全部楼层
实际的sql要复杂许多,而且很多地方会涉及到需要分批次提交

使用道具 举报

回复
论坛徽章:
1
优秀写手
日期:2014-10-24 06:00:14
 楼主| 发表于 2014-11-28 21:46 | 显示全部楼层
难道forall不能和nologging结合使用吗?

使用道具 举报

回复
论坛徽章:
520
奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:532009日食纪念
日期:2009-07-22 09:30:00
发表于 2014-11-28 22:19 | 显示全部楼层
donglin243870 发表于 2014-11-28 21:46
难道forall不能和nologging结合使用吗?

看起来是这样,所以我建议你把数组改用嵌套表实现。其他逻辑都不用改,就是最后用TABLE()来传数据。

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 未成年人举报专区 
京ICP备16024965号-8  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表