需要在一个1亿行的大表中,删除1千万行数据。您有什么好的方案。
需求是在对数据库其他应用影响最小的情况下,以最快的速度完成。操作期间应该注意什么。
如果中途中止了,有无方法再继续。
这种大型操作相信我们偶尔都会遇到,希望大家各抒己见,讨论出一个或几个最佳实践,方便自己以后操作,也方便后来者。
如果业务无法停止的话,主要有这三种思路:
=======================================================================================================
思路1:根据ROWID分片、再利用Rowid排序、批量处理、回表删除。
在业务无法停止的时候,选择这种方式,的确是最好的。一般可以控制在每一万行以内提交一次,不会对回滚段造成太大压力(我在做大DML时,通常选
择一两千行一提交)。选择业务低峰时做,对应用也不至于有太大影响。
感谢htyansp,在49楼提供了一个很简捷的脚本,如果你对这种方式不熟悉,可以参考此脚本:
序。这种方式的另一个优点就是还可以并行。
有一次我需要删除DW库一个大表中满足条件的行。应用方保证不会再出现此条件的行,我只需要在几天内,将所有满足条件的行删除完即可。此表所在
的表空间有几十个数据文件(每个文件32G),我用如下的命令生成表在每个文件中行的ROWID范围:
然后,根据上面得到的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的行,最基本的存储过程如下:
这种方式也可以根据ROWID分片,只会对表进行一次扫描。但没有批量处理,性能反而不如上面。
=======================================================================================================
思路三: ON PREBUILT物化视图方法
这种方式,阿里迁移数据的确使用较多,也是一种不错的方式。速度没有方法一快,但比较简单,而且对业务基本上没有影响。另外,对于删除操作,可以释放
删除过的空间。缺点就是需要有主键。
假设目标表是P3,主键列是ID1,要删除ID2列于小1000的行:
步1,建立中间表p3_m:
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、如果表空间可以设为只读,还有坛友的表空间迁移,迁到测试平台,慢慢删除再导入回来的方法
等等。可以停应用的方法就很多了。
需求是在对数据库其他应用影响最小的情况下,以最快的速度完成。操作期间应该注意什么。
如果中途中止了,有无方法再继续。
这种大型操作相信我们偶尔都会遇到,希望大家各抒己见,讨论出一个或几个最佳实践,方便自己以后操作,也方便后来者。
如果业务无法停止的话,主要有这三种思路:
=======================================================================================================
思路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、如果表空间可以设为只读,还有坛友的表空间迁移,迁到测试平台,慢慢删除再导入回来的方法
等等。可以停应用的方法就很多了。