我的表结构大致如下: 表一:CUS.INFO(ID,AGE,TIME,SERVICE) 表二:CUS,INFO_SET(ID,INFO_ID,PASSWORD) 表三:CUS.INFO_FILE(ID,INFO_ID,FILE_NAME,FILE_POSITION) 后两张表的INFO_ID与表一的ID关联。首先删表二、表三 sql语句分别为: delete from INFO_SET where INFO_ID in(select id from INFO where TIME BETWEEN 1 and xxxxxxxxx); delete from INFO_FILE where INFO_ID in(select id from INFO where TIME BETWEEN 1 and xxxxxxxxx); xxxxxxxxx是符合条件的时间。 最后删表一 delete from INFO where TIME BETWEEN 1 and xxxxxxxxx;表二、表三很快,表一很慢,测试过,肯定是能用,一万条记录我测试过,用了两分钟。另外,我删旧数据的同时,别人还在往这几张表插入新的记录。 释放存储空间如何解决?
我曾经遇到过和你相似的情况,用得是DELPHI,不过数据量比你大得多,也没你那么慢~ 我当时的情况是删除一年的数据(千万级),一次要删除十几个小时。 我采取的办法是一:做了一个循环,一年分成12个月,一个月一个月分批删除; 二:根据你这些语句,你这2个表肯定是有主外建关联的,如非必要,建议取消关联,速度会提升很多; 我的一点个人经验,希望能对你有所帮助delete from INFO_SET where INFO_ID in(select id from INFO where TIME BETWEEN 1 and xxxxxxxxx); delete from INFO_FILE where INFO_ID in(select id from INFO where TIME BETWEEN 1 and xxxxxxxxx
/--先作一个存贮过程:CREATE OR REPLACE PROCEDURE User1.pro_del AS begin delete from INFO_SET where INFO_ID in (select id from INFO where TIME BETWEEN 1 and xxxxxxxxx); delete from INFO_FILE where INFO_ID in (select id from INFO where TIME BETWEEN 1 and xxxxxxxxx); delete from INFO where TIME BETWEEN 1 and xxxxxxxxx;END; /--再作一个JOB:begin sys.dbms_job.submit(job => :job, what => 'User1.pro_del;', next_date => to_date('18-10-2008', 'dd-mm-yyyy'), interval => 'TRUNC(SYSDATE+1)'); commit; end; /
大量数据的删除,可以考虑分批删除、提交。
贴表结构和删除的SQL语句!
表一:CUS.INFO(ID,AGE,TIME,SERVICE)
表二:CUS,INFO_SET(ID,INFO_ID,PASSWORD)
表三:CUS.INFO_FILE(ID,INFO_ID,FILE_NAME,FILE_POSITION)
后两张表的INFO_ID与表一的ID关联。首先删表二、表三
sql语句分别为:
delete from INFO_SET where INFO_ID in(select id from INFO where TIME BETWEEN 1 and xxxxxxxxx);
delete from INFO_FILE where INFO_ID in(select id from INFO where TIME BETWEEN 1 and xxxxxxxxx);
xxxxxxxxx是符合条件的时间。
最后删表一
delete from INFO where TIME BETWEEN 1 and xxxxxxxxx;表二、表三很快,表一很慢,测试过,肯定是能用,一万条记录我测试过,用了两分钟。另外,我删旧数据的同时,别人还在往这几张表插入新的记录。
释放存储空间如何解决?
ALert.log文件,看看有没有出错信息,估计是hung死了
我当时的情况是删除一年的数据(千万级),一次要删除十几个小时。
我采取的办法是一:做了一个循环,一年分成12个月,一个月一个月分批删除;
二:根据你这些语句,你这2个表肯定是有主外建关联的,如非必要,建议取消关联,速度会提升很多;
我的一点个人经验,希望能对你有所帮助delete from INFO_SET where INFO_ID in(select id from INFO where TIME BETWEEN 1 and xxxxxxxxx);
delete from INFO_FILE where INFO_ID in(select id from INFO where TIME BETWEEN 1 and xxxxxxxxx
/--先作一个存贮过程:CREATE OR REPLACE PROCEDURE User1.pro_del AS
begin delete from INFO_SET
where INFO_ID in
(select id from INFO where TIME BETWEEN 1 and xxxxxxxxx);
delete from INFO_FILE
where INFO_ID in
(select id from INFO where TIME BETWEEN 1 and xxxxxxxxx);
delete from INFO where TIME BETWEEN 1 and xxxxxxxxx;END;
/--再作一个JOB:begin
sys.dbms_job.submit(job => :job,
what => 'User1.pro_del;',
next_date => to_date('18-10-2008', 'dd-mm-yyyy'),
interval => 'TRUNC(SYSDATE+1)');
commit;
end;
/
你的表肯定有问题了......找下v$session_wait 等待事件..
在数据库中执行比用程序执行的效率要高。且楼主在8楼说"每天都要定时删",使用JOB定时执行是最简捷的,用vc基本上做不了的。