我的问题是,我似乎找不到一个有效的方式来实际执行删除.为了我的测试目的,我正在运行7384行删除单个测试表,该测试表有大约9400行.我在Oracle sql Developer中测试了许多可能的查询解决方案：
7384个独立的DELETE语句花了203秒：
delete from TABLE1 where ID=1000001356443294; delete from TABLE1 where ID=1000001356443296; etc...
7384个单独的SELECT语句花了57秒：
select ID from TABLE1 where ID=1000001356443294 select ID from TABLE1 where ID=1000001356443296 etc...
7384从(SELECT)语句中分离DELETE需要214秒：
delete from (select ID from TABLE1 where ID=1000001356443294); delete from (select ID from TABLE1 where ID=1000001356443296); etc...
1 SELECT语句中有7384个OR子句在哪里拿了127.4s：
select ID from TABLE1 where ID=1000001356443294 or ID = 1000001356443296 or ...
1 DELETE from(SELECT)语句中有7384个OR子句在哪里花了74.4s：
delete from (select ID from TABLE1 where ID=1000001356443294 or ID = 1000001356443296 or ...)
虽然最后一个可能是最快的,但进一步的测试仍然非常缓慢,从9000行表到甚至只有一个20万行表(仍然是最终的表集大小的1％),同样的语句需要14分钟跑步.而>每行更快50％,当对完整的数据集运行时,它仍然推断出大约一天.我有一个很好的权威,我们用来做这个任务的软件可以在大约20分钟内完成.
所以我的问题是：
- Is there a better way to delete?
- Should I use a round of
SELECTstatements (i.e.,like the second test) to discover which table any given row is in and then shoot off delete queries? Even that looks quite slow but…- Is there anything else I can do to speed the deletes up? I don’t have DBA-level access or knowledge.
尽量减少发表的数量和相关工作.
所有情况都假设您有一个表(PURGE_IDS)从TABLE_1,TABLE_2等中删除.
考虑使用CREATE TABLE AS SELECT进行大量删除
如果没有并发活动,而您正在删除一个或多个表中的30％的行,请勿删除;执行创建表作为选择与您希望保留的行,并交换旧表格为旧表. INSERT / * APPEND * / …如果您负担得起,NOLOGGING是非常便宜的.即使您有一些并发活动,也可以使用在线表重新定义来重建表格.
不要运行DELETE语句,你知道不会删除任何行
如果在六个表格中的最多一个中存在ID值,那么请记住您删除了哪些ID,而不要尝试从任何其他表格中删除这些ID.
CREATE TABLE TABLE1_PURGE NOLOGGING AS SELECT ID FROM PURGE_IDS INNER JOIN TABLE_1 ON PURGE_IDS.ID = TABLE_1.ID; DELETE FROM TABLE1 WHERE ID IN (SELECT ID FROM TABLE1_PURGE); DELETE FROM PURGE_IDS WHERE ID IN (SELECT ID FROM TABLE1_PURGE); DROP TABLE TABLE1_PURGE;
并重复.
管理并发如果你必须
另一种方法是使用PL / sql循环遍历表,发出rowcount限制的delete语句.如果针对正在运行删除的表有重大的插入/更新/删除并发负载,则这很可能是适用的.
declare l_sql varchar2(4000); begin for i in (select table_name from all_tables where table_name in ('TABLE_1','TABLE_2',...) order by table_name); loop l_sql := 'delete from ' || i.table_name || ' where id in (select id from purge_ids) ' || ' and rownum <= 1000000'; loop commit; execute immediate l_sql; exit when sql%rowcount <> 1000000; -- if we delete less than 1,000,000 end loop; -- no more rows need to be deleted! end loop; commit; end;
