从Oracle删除指定行的最佳方法 [英] Optimal way to DELETE specified rows from Oracle

问题描述

我有一个项目，偶尔需要从六个大小不同的表之一中删除数万行，但它们之间大约有3000万行.由于给出的数据结构原因，我不知道六个表中的哪个表中有需要删除的行，因此我必须对所有表运行所有删除操作.我已经针对ID列构建了一个INDEX来尝试加快处理速度，但是如果这样做可以加快处理速度，则可以将其删除.

I have a project that needs to occasionally delete several tens of thousands of rows from one of six tables of varying sizes but that have about 30million rows between them. Because of the structure of the data I've been given, I don't know which of the six tables has the row that needs to be deleted in it so I have to run all deletes against all tables. I've built an INDEX against the ID column to try and speed things up, but it can be removed if that'll speed things up.

我的问题是，我似乎找不到真正执行删除操作的有效方法.出于测试目的，我针对具有约9400行的单个测试表运行了7384个删除行.我已经在Oracle SQL Developer中测试了许多可能的查询解决方案:

My problem is, that I can't seem to find an efficient way to actually perform the delete. For the purposes of my testing I'm running 7384 delete rows against single test-table which has about 9400 rows. I've tested a number of possible query solutions in Oracle SQL Developer:

7384个单独的DELETE语句花费了 203 秒:

7384 separate DELETE statements took 203 seconds:

delete from TABLE1 where ID=1000001356443294;
delete from TABLE1 where ID=1000001356443296;
etc...

7384个单独的SELECT语句花费了 57 秒:

7384 separate SELECT statements took 57 seconds:

select ID from TABLE1 where ID=1000001356443294
select ID from TABLE1 where ID=1000001356443296
etc...

7384个单独的DELETE from (SELECT)语句花费了 214 秒:

7384 separate DELETE from (SELECT) statements took 214 seconds:

delete from (select ID from TABLE1 where ID=1000001356443294);
delete from (select ID from TABLE1 where ID=1000001356443296);
etc...

1 SELECT语句在其中花费了 127.4s 的地方有7384个OR子句:

1 SELECT statement that has 7384 OR clauses in the where took 127.4s:

select ID from TABLE1 where ID=1000001356443294 or ID = 1000001356443296 or ...

1 DELETE from (SELECT)语句在其中花费了 74.4s 的地方有7384个OR子句:

1 DELETE from (SELECT) statement that has 7384 OR clauses in the where took 74.4s:

delete from (select ID from TABLE1 where ID=1000001356443294 or ID = 1000001356443296 or ...)

尽管最后一个可能是最快的，但在进一步测试时，从9000行表扩展到仅200,000行表(仍小于最终表集大小的1％)时，它仍然非常慢语句需要 14分钟运行.虽然每行速度快50％以上，但在对整个数据集运行时，最多仍可以推断出一天的时间.我有很好的权威，认为我们用来完成此任务的软件可以在大约20分钟内完成.

While the last may be the fastest, upon further testing its still very slow when scaled up from the 9000 row table to even just a 200,000 row table (which is still < 1% of the final tableset size) where the same statement takes 14mins to run. While > 50% faster per row, that still extrapolates up to about a day when being run against the full dataset. I have it on good authority that the piece of software we used to us to do this task could do it in about 20mins.

所以我的问题是:

• 是否有更好的删除方法?
• 我是否应该使用一轮SELECT语句(即类似于第二项测试)来发现给定行所在的表，然后启动删除查询?即使这样看起来也很慢，但是...
• 我还有其他方法可以加快删除速度吗?我没有DBA级别的访问权限或知识.

• Is there a better way to delete?
• Should I use a round of SELECT statements (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.

推荐答案

在回答我的问题之前，这是我的处理方式:

In advance of my questions being answered, this is how I'd go about it:

使陈述的数量及其所做的相对工作减至最少.

Minimize the number of statements and the work they do issued in relative terms.

所有情况都假定您具有要从TABLE_1，TABLE_2等中删除的ID表(PURGE_IDS).

All scenarios assume you have a table of IDs (PURGE_IDS) to delete from TABLE_1, TABLE_2, etc.

考虑使用CREATE TABLE AS SELECT进行大型删除

如果没有并发活动，并且您要删除一个或多个表中30％以上的行，请不要删除；对要保留的行执行create table as select，然后将新表换为旧表.如果您负担得起的话，INSERT /*+ APPEND */ ... NOLOGGING的价格出奇的便宜.即使您确实有一些并发活动，您也可以使用Online Table Redefinition来就地重建表.

If there's no concurrent activity, and you're deleting 30+ % of the rows in one or more of the tables, don't delete; perform a create table as select with the rows you wish to keep, and swap the new table out for the old table. INSERT /*+ APPEND */ ... NOLOGGING is surprisingly cheap if you can afford it. Even if you do have some concurrent activity, you may be able to use Online Table Redefinition to rebuild the table in-place.

不要运行您知道不会删除任何行的DELETE语句

如果六个表中最多有一个ID值，请跟踪已删除的ID-不要尝试从其他任何表中删除这些ID.

If an ID value exists in at most one of the six tables, then keep track of which IDs you've deleted - and don't try to delete those IDs from any of the other tables.

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循环，发出行数限制的delete语句.如果对要运行删除操作的表进行大量的插入/更新/删除并发负载，则这很可能是合适的.

Another way is to use PL/SQL looping over the tables, issuing a rowcount-limited delete statement. This is most likely appropriate if there's significant insert/update/delete concurrent load against the tables you're running the deletes against.

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;

这篇关于从Oracle删除指定行的最佳方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！