查看: 35987|回复: 140

(内附总结的讨论结果)大DML最佳实践讨论,参与即有机会获赠oracle rac日记一本!

[复制链接]
论坛徽章:
70
夏利
日期:2013-09-29 21:02:15天蝎座
日期:2016-03-08 22:25:51嫦娥
日期:2014-03-04 16:46:45ITPUB年度最佳技术原创精华奖
日期:2014-03-04 16:19:29马上加薪
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有车
日期:2014-02-19 11:55:14马上有车
日期:2014-02-18 16:41:11
发表于 2012-4-27 10:00 | 显示全部楼层 |阅读模式
需要在一个1亿行的大表中,删除1千万行数据。您有什么好的方案。
需求是在对数据库其他应用影响最小的情况下,以最快的速度完成。操作期间应该注意什么。
如果中途中止了,有无方法再继续。

这种大型操作相信我们偶尔都会遇到,希望大家各抒己见,讨论出一个或几个最佳实践,方便自己以后操作,也方便后来者。

活动时间:
4月26日—5月18日

活动奖励:
活动结束,我们选出讨论最积极的5位会员,赠送oracle rac日记一本

{E5F1F49D-FFB6-4F54-9212-3AFD9AC4DCF8}.jpg

感谢caibird2005、htyansp、xgghxkhuang、张兵1989、xb0307052,书只有5本,只能随机选了。

如果业务无法停止的话,主要有这三种思路:
=======================================================================================================
思路1:根据ROWID分片、再利用Rowid排序、批量处理、回表删除。
        在业务无法停止的时候,选择这种方式,的确是最好的。一般可以控制在每一万行以内提交一次,不会对回滚段造成太大压力(我在做大DML时,通常选
择一两千行一提交)。选择业务低峰时做,对应用也不至于有太大影响。
        感谢htyansp,在49楼提供了一个很简捷的脚本,如果你对这种方式不熟悉,可以参考此脚本:
declare  
   cursor mycursor is SELECT  ROWID FROM TEST WHERE  XXX=XXXX  order by rowid;   <--------按ROWID排序的Cursor,删除条件是XXX=XXXX,根据实际情
况来定。
   type rowid_table_type is  table  of rowid index by pls_integer;
   v_rowid   rowid_table_type;
BEGIN
   open mycursor;
   loop
     fetch   mycursor bulk collect into v_rowid  limit 5000;   <--------每次处理5000行,也就是每5000行一提交
     exit when v_rowid.count=0;
     forall i in v_rowid.first..v_rowid.last
        delete from test  where rowid=v_rowid(i);
     commit;
   end loop;
   close mycursor;
END;
/
        这种方法的缺点是排序有可能会消耗太多临时表空间。还有一种方式,先根据Rowid分片。将一个大表用Rowid划分成多个部分,每部分单独根据Rowid排
序。这种方式的另一个优点就是还可以并行。
        有一次我需要删除DW库一个大表中满足条件的行。应用方保证不会再出现此条件的行,我只需要在几天内,将所有满足条件的行删除完即可。此表所在
的表空间有几十个数据文件(每个文件32G),我用如下的命令生成表在每个文件中行的ROWID范围:
select dbms_rowid.ROWID_CREATE(1,12227,file_id,MIN(BLOCK_ID),0),dbms_rowid.ROWID_CREATE(1,12227,file_id,MAX(BLOCK_ID+BLOCKS-1),8192) from
dba_extents where segment_name='DML_TST' group by file_id order by file_id;
此命令中DATA_OID是dba_objects 中data_object_id列值。
        然后,根据上面得到的ROWID范围操作目标表。其实就是将htyansp的存储过程中第二行,根据生成的ROWID修改如下:
        cursor mycursor is SELECT  ROWID FROM TEST WHERE  rowid between 'ROWID' and  'ROWID' and XXX=XXXX order by rowid;
        
        存储过程其他行基本不变。
        搞几十个这样的存储过程,开几个会话并行着跑。
        另外,TOM在9i&10G编程艺术 648页到652页有一个很好的例子,其中650页自动生成ROWID部分,可以参考。
        使用这种方式最大的优点就是性能可控,需要快点的话,可以多设几个并行。想慢点的,并行就少点。而且,一次处理的行数有限,对ROWID的排序不会
撑爆临时表空间。
=======================================================================================================
思路二:根据ROWID分片、非批量处理、回表删除
        比如,要删除dml_tst中ID等于Value的行,最基本的存储过程如下:
declare
        CURSOR test2_cs(value number,rid1 rowid,rid2 rowid)
          IS SELECT id from dml_tst
          where id=value and rowid between rid1 and  rid2
          FOR UPDATE ;
        k number:=0;
BEGIN
   FOR c1_rec IN test2_cs(3338,'AAAC/DAAEAAAABJAAA','AAAC/DAAEAAAABQCAA') LOOP
            delete dml_tst where CURRENT OF test2_cs;
   END LOOP;
END;
/
        这种方式也可以根据ROWID分片,只会对表进行一次扫描。但没有批量处理,性能反而不如上面。
=======================================================================================================
思路三: ON PREBUILT物化视图方法

这种方式,阿里迁移数据的确使用较多,也是一种不错的方式。速度没有方法一快,但比较简单,而且对业务基本上没有影响。另外,对于删除操作,可以释放
删除过的空间。缺点就是需要有主键。
假设目标表是P3,主键列是ID1,要删除ID2列于小1000的行:
步1,建立中间表p3_m:
create table p3_m as select * from p3 where 0=1;
步2,建产和中间表同名的物化视图,一定要有ON PREBUILT选项:
CREATE MATERIALIZED VIEW p3_m
ON PREBUILT TABLE AS
select * from p3 where id2>=1000;  <--------将不满足删除条件的行放入物化视图
步3:添加物化视图日志:
CREATE MATERIALIZED VIEW LOG ON p3 WITH PRIMARY KEY,sequence (id2,id3,cc1,cc2) INCLUDING NEW VALUES;
步4:在数据库空闲的时候,进行一次完全刷新:
exec dbms_mview.refresh('P3_M','C');
完全刷新后,可以在中间表上创建和目标表一样的索引、约束等等
步5:进行个一、两次增量刷新:
exec dbms_mview.refresh('P3_M','F');
步6:将原表锁住,最后进行一次增量刷新,然后马上Rename目标表为其他名字
lock table p3 in EXCLUSIVE mode;
exec dbms_mview.refresh('P3_M','F');
drop MATERIALIZED VIEW LOG ON p3;
alter table p3 rename to p3_n;
步7:删除物化视图,修改中间表为原目标表的名字:
drop MATERIALIZED VIEW p3_m;
alter table p3_m rename to p3;
步8:确定原表如果没有用了,可以删除改过名的原表
也可以使用再线重定义,思路和这个类似。
======================================================================================
        如果不影响应用的话,常规方法也就这些了。这三种思路,也可以用于Update。
        根据这三种思路,我们可以结合自身应用情况加以改变。总能找到一款适合应用的方法。
        注意事项是
        1、注意备份
        2、千万注意不要太猛,曾经有一次同事因为Update的太猛,影响了我们一个重要的前台应用。一定要注意,一次提交的行数不能太高。

        如果应用可以停,哪方法就太多了。
1、CTAS的方法创建一个新表,排除要DELETE的数据,再改名。为提高速度,还可以禁用索引,DML完再重建。
2、只导出不删除的数据,再导入,再改名
3、如果表空间可以设为只读,还有坛友的表空间迁移,迁到测试平台,慢慢删除再导入回来的方法
等等。可以停应用的方法就很多了。
论坛徽章:
3
双黄蛋
日期:2012-03-28 17:17:32复活蛋
日期:2012-06-12 18:45:45奥运会纪念徽章:摔跤
日期:2012-06-20 16:40:26
发表于 2012-4-27 10:04 | 显示全部楼层
如果表未分区又有足够的磁盘空间,能否使用在线重定义的方法,把表转成分区表,然后把其中包含有1000W左右的分区直接drop掉!

使用道具 举报

回复
论坛徽章:
70
夏利
日期:2013-09-29 21:02:15天蝎座
日期:2016-03-08 22:25:51嫦娥
日期:2014-03-04 16:46:45ITPUB年度最佳技术原创精华奖
日期:2014-03-04 16:19:29马上加薪
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有车
日期:2014-02-19 11:55:14马上有车
日期:2014-02-18 16:41:11
 楼主| 发表于 2012-4-27 10:09 | 显示全部楼层
youshang44 发表于 2012-4-27 10:04
如果表未分区又有足够的磁盘空间,能否使用在线重定义的方法,把表转成分区表,然后把其中包含有1000W左右的分 ...

可以,这是一种方案。

使用道具 举报

回复
论坛徽章:
19
ITPUB十周年纪念徽章
日期:2011-11-01 16:26:292015年新春福章
日期:2015-03-04 14:53:16优秀写手
日期:2014-03-19 06:00:24马上有对象
日期:2014-02-18 16:44:082014年新春福章
日期:2014-02-18 16:44:08比亚迪
日期:2013-10-23 21:35:02ITPUB社区12周年站庆徽章
日期:2013-10-08 14:54:39茶鸡蛋
日期:2013-07-25 19:48:40灰彻蛋
日期:2013-05-24 09:42:412013年新春福章
日期:2013-02-25 14:51:24
发表于 2012-4-27 10:25 | 显示全部楼层
本帖最后由 ahdong2007 于 2012-4-27 10:26 编辑

RAC日记,我好想要喔!

a. delete,很多人都会喷,但是,这是最容易想到的方法,不复杂,只是副作用大
b. 11g的话,不删数据,加列is_deleted,只是需要修改业务SQL,副作用也大,比如不能减少存储的数据量了,不能减少索引的数据量了;那一天发神经还想要回历史数据,也变得容易
c. 禁用索引后再进行删除操作(1KW还是很快的),然后重建索引(这个要点时间),还必须停业务
d. 写存储过程,匹配rowid来删除,这个方法还是非常快
e. CTAS nologging, append还是很快的,就是,得停业务,只是DG环境nologging无效
f. 根据删除条件,重定向表为分区表,然后直接drop partition,变dml操作为ddl操作

如果失败:dml操作直接就rollback了,换其他方案;如果是ddl操作,比如重定向表,现在测试环境试几次再上生产环境吧

使用道具 举报

回复
论坛徽章:
30
红宝石
日期:2012-10-10 14:50:58技术图书徽章
日期:2017-08-17 11:00:25奥运会纪念徽章:举重
日期:2016-09-18 11:29:42马上有钱
日期:2014-04-17 16:12:27马上加薪
日期:2014-03-31 14:47:51技术图书徽章
日期:2014-03-31 10:52:30优秀写手
日期:2013-12-18 09:29:162013年新春福章
日期:2013-02-25 14:51:24蓝锆石
日期:2012-10-10 14:50:58萤石
日期:2012-10-10 14:50:58
发表于 2012-4-27 10:28 | 显示全部楼层
如果没有主健,是否可以expdp,impdp测试库,慢慢删,生产库drop,把删过的expdp,然后impdp进生产库。
这样很慢,但是安全

使用道具 举报

回复
论坛徽章:
171
ITPUB社区OCM联盟徽章
日期:2013-07-30 11:25:46最佳人气徽章
日期:2013-03-19 17:13:45ITPUB年度最佳技术原创精华奖
日期:2013-03-22 13:18:30ITPUB季度 技术新星
日期:2012-05-22 15:10:11BLOG每日发帖之星
日期:2012-02-15 16:43:07生肖徽章2007版:马
日期:2012-03-07 10:13:26蓝锆石
日期:2012-02-24 10:13:15萤石
日期:2012-02-24 10:13:15海蓝宝石
日期:2012-02-24 10:13:15紫水晶
日期:2012-03-01 21:28:36
发表于 2012-4-27 10:30 | 显示全部楼层
这个活动好~
我查了查资料,觉得:
1、大规模DML数据表,需要考虑日志容量、速度,脏数据保存时间等等很多问题。如果你不计较时间,只是删除数据,建议写个脚本,用游标提取数据后,一批一批的提交删除,慢是慢了点,但肯定不影响业务,最好晚上做(业务小的时候)

2、如果业务是24X7运行的话,可以采用分批删除的方法
bulk collect into xxx limit xx
forall ....
  delete .....

分批删除,分批提交不错,在系统不太繁忙的情况下可以这样

3、同样建议 备份需要的数据 然后建立新表导入 旧的直接删除算了 或者找个地方保存 现在存储介质都便宜了
删除这么多的数据,就没有删除的必要了,直接新建另一张表,把原表中需要的数据全部插到新表里面去,做次全表扫描。然后将原来的表drop,在修改新表表名为原表。

·····
期待大神们发表自己的看法~

使用道具 举报

回复
论坛徽章:
2
灰彻蛋
日期:2012-04-06 18:05:252013年新春福章
日期:2013-02-25 14:51:24
发表于 2012-4-27 10:31 | 显示全部楼层
对于1亿的数据量,传统的bulk delete,forall ,for loop等等,效果都不行,只有分而制之,可行的方法有:
1.做分区表,逐个分区处理
2.按照表里面的条件,大概100000条记录一个循环,处理完立即提交。
3.如果磁盘很大,可以用磁盘换性能,直接用insert替代delete,把满足条件的行记录,插入到新表,insert的时候还是要用循环,分十万~百万条记录左右,提交一次。

使用道具 举报

回复
论坛徽章:
0
发表于 2012-4-27 10:31 | 显示全部楼层
快乐的大个子 发表于 2012-4-27 10:28
如果没有主健,是否可以expdp,impdp测试库,慢慢删,生产库drop,把删过的expdp,然后impdp进生产库。
这 ...

导出去删过之后 业务不停的话,数据就不同步了

使用道具 举报

回复
论坛徽章:
30
红宝石
日期:2012-10-10 14:50:58技术图书徽章
日期:2017-08-17 11:00:25奥运会纪念徽章:举重
日期:2016-09-18 11:29:42马上有钱
日期:2014-04-17 16:12:27马上加薪
日期:2014-03-31 14:47:51技术图书徽章
日期:2014-03-31 10:52:30优秀写手
日期:2013-12-18 09:29:162013年新春福章
日期:2013-02-25 14:51:24蓝锆石
日期:2012-10-10 14:50:58萤石
日期:2012-10-10 14:50:58
发表于 2012-4-27 10:46 | 显示全部楼层
kkaaron 发表于 2012-4-27 10:31
导出去删过之后 业务不停的话,数据就不同步了

哦。。对哦,疏漏了。。

使用道具 举报

回复
论坛徽章:
1
ITPUB十周年纪念徽章
日期:2011-11-01 16:24:04
发表于 2012-4-27 10:48 | 显示全部楼层
因为要在对数据库其他应用影响最小的情况下做,那么就不要直接对表进行删除数据的操作,如果直接对表进行删除则会出现应用因为等待数据释放锁而等待因此考虑了下
可以使用不追加日志create table as select 创建表,然后在创建表上采用Rowid删除要删除的数据,然后最后将原表删除,把新表重命名

使用道具 举报

回复

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

本版积分规则 发表回复

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