快速删除大表中的大量数据

近日，碰到一个案例，一个朋友在一张大表上删除大量的数据，最终得以快速的解决，不敢独享，共享之。[@more@]

下面是朋友发过来的邮件：

#################################################
首先，4号使用的删除语句如下：
DELETE FROM R_FORM_FIELD_TEMP_VALUE
WHERE TASK_NO NOT IN
(
SELECT DISTINCT TASK_NO
FROM R_TASK
)
由存储过程在晚上12点时调用，结果很惨，到第二天下午16：00的时候，还在删除。等于一直删了十几个小时，没办法，就人工给停了，导致了一些作业需要重新录入。
使用了一个临时表，首先将有用的数据存到临时表中，然后把原表的所有数据删除，再把临时表的数据重新插入到原表中。
语句如下：
TRUNCATE TABLE dbo.R_FORM_FIELD_TEMP_VALUE_BAK
INSERT INTO dbo.R_FORM_FIELD_TEMP_VALUE_BAK
SELECT *
FROM dbo.R_FORM_FIELD_TEMP_VALUE
WHERE TASK_NO IN
(
SELECT TASK_NO
FROM R_TASK
)
TRUNCATE TABLE R_FORM_FIELD_TEMP_VALUE
INSERT INTO dbo.R_FORM_FIELD_TEMP_VALUE
SELECT *
FROM dbo.R_FORM_FIELD_TEMP_VALUE_BAK

该语句执行时间在1分钟之内，昨天把新语句换上去，今天早上查询数据量保持在50w以内，属于正常范围；作业可以正常申请。

#################################################
后来我自己总结归纳，做了以下测试,共分4步：
1，生成测试数据：
--建表
create table source_t(id int primary key,name varchar(20) not null, deptno int);
--生成模拟数据，消耗性能较大
declare
i number;
begin
for i in 1..16460000 loop
case mod(i,3)
when 0 then
insert into SOURCE_T values(i,'能被3整除的',i);
when 1 then
insert into SOURCE_T values(i,'被3整除,余1的',i);
else
insert into SOURCE_T values(i,'被3整除,余2的',i);
end case;
if mod(i,10000)=0 then commit; end if;
end loop;
end;
--共模拟16460000条记录
SQL> select count(1) from source_t;

COUNT(1)
----------
16460000

2，生成目的表，同时把需要保留的数据导入备份表

create table dest_t as select id,name,deptno from source_t where mod(id, 3) = 0;
--已完成，耗时72.203秒
SQL> select count(1) from dest_t;

COUNT(1)
----------
5486666
3，备份原表SOURCE_T，有两种方法，一是用EXP导出至.DMP文件，然后DROP SOURCE_T；二是更改SOURCE_T的表名，如：SOURCE_T_BAK.

4，更改dest_t的表名为原表表名：rename dest_t to source_t;并且把与表source_t相关的触发器、存储过程等重新编译（切记）。

DBA别以为可以松口气了，呵呵！还有些后续工作呢。
新建source_t上的主键、索引、check约束等;
对source_t表进行分析：analyze table source_t compute statistics; analyze index index_name estimate statistics;
其他一些细节......

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14758327/viewspace-1028531/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14758327/viewspace-1028531/