oracle清除试图的数据,ORACLE大表中删除部分数据最佳方案

如果业务无法停止的话，主要有这三种思路：=======================================================================================================

思路1：根据ROWID分片、再利用Rowid排序、批量处理、回表删除。        在业务无法停止的时候，选择这种方式，的确是最好的。一般可以控制在每一万行以内提交一次，不会对回滚段造成太大压力(我在做大DML时，通常选

择一两千行一提交)。选择业务低峰时做，对应用也不至于有太大影响。

感谢htyansp，在49楼提供了一个很简捷的脚本，如果你对这种方式不熟悉，可以参考此脚本：

declare

cursor mycursor is SELECT  ROWID FROM TEST WHERE  XXX=XXXX  order by rowid;   

况来定。

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;   

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：确定原表如果没有用了，可以删除改过名的原表

也可以使用再线重定义，思路和这个类似。

在线重定义案件：http://www.askmaclean.com/archives/%e5%88%a9%e7%94%a8oracle%e5%9c%a8%e7%ba%bf%e9%87%8d%e5%ae%9a%e4%b9%89online-redefinition%e6%b8%85%e7%90%86%e5%8e%86%e5%8f%b2%e6%95%b0%e6%8d%ae.html

======================================================================================

如果不影响应用的话，常规方法也就这些了。这三种思路，也可以用于Update。

根据这三种思路，我们可以结合自身应用情况加以改变。总能找到一款适合应用的方法。

注意事项是

1、注意备份

2、千万注意不要太猛，曾经有一次同事因为Update的太猛，影响了我们一个重要的前台应用。一定要注意，一次提交的行数不能太高。

如果应用可以停，哪方法就太多了。1、CTAS的方法创建一个新表，排除要DELETE的数据，再改名。为提高速度，还可以禁用索引，DML完再重建。 2、只导出不删除的数据，再导入，再改名 3、如果表空间可以设为只读，还有坛友的表空间迁移，迁到测试平台，慢慢删除再导入回来的方法 等等。可以停应用的方法就很多了。