从Oracle中删除指定行的最佳方式

Question

我有一个项目，偶尔需要从六个不同大小的表中删除数万行数据，总共这些表大约有3000万行数据。由于我得到的数据结构，我不知道哪个表包含需要被删除的行，因此必须对所有表运行所有删除操作。我已经针对ID列建立了索引以尝试加快速度，但如果可以提高速度，则可以删除它。

我的问题是，我似乎找不到一种有效的方法来执行删除操作。为了进行测试，我在Oracle SQL Developer中针对具有约9400行的单个测试表运行了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个DELETE from (SELECT)语句，耗时214秒：

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

在WHERE语句中有7384个OR逻辑运算符的SELECT查询花费了127.4秒：

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

在where语句中有7384个OR条件的DELETE from (SELECT)语句执行了74.4秒:

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

虽然最后一个方法可能是最快的，但在进一步测试时，即使从9000行表扩展到仅200,000行表（仍然不到最终表格集大小的1％），相同的语句运行需要14分钟。虽然每行要快50％以上，但当针对完整数据集运行时，大约需要一天时间。有可靠消息称，我们用来执行此任务的软件可以在约20分钟内完成。

所以我的问题是：

• 是否有更好的删除方法？
• 我应该使用一轮SELECT语句（例如第二个测试）发现任何给定行所在的表，然后发送删除查询吗？即使如此看起来也很慢，但...
• 我还能做些什么来加速删除吗？我没有DBA级别的访问权限或知识。

- GIS-Jonathan

---

尝试执行以下操作：delete from TABLE1 where ID in (1000001356443294,1000001356443296,...) - CapelliC

你将从这六个表中删除三千万行数据。每个表大概会剩下多少行数据？在此期间需要支持多少并发写入活动？ - Adam Musch

@chac - 刚试了一下，不起作用。我收到了错误信息：ORA-01795: 列表中的表达式数量已达到最大值1000（仅在此测试查询中就有超过7000个）。 - GIS-Jonathan

删除1000需要多少时间？这应该大致呈线性比例... - CapelliC

@Chac - 3秒。哇。假设线性比例，整个过程大约需要22秒，比下一个最快的快3倍！当然，我不确定我正在使用的工具是否允许我轻松地将其拆分为1000个项目列表，但这可能比其他解决方案更可行。谢谢。 - GIS-Jonathan

Answer 1

在我的问题得到回答之前，这是我的做法：

尽量减少相对语句中发出的语句数量和它们所执行的工作量。

所有情况都假设您有一个包含要从 TABLE_1、TABLE_2 等表中删除的 ID 的表 (PURGE_IDS)。

考虑使用 CREATE TABLE AS SELECT 处理大规模删除

如果没有并发活动，并且您要删除一个或多个表中 30% 以上的行，请不要直接删除；使用您希望保留的行进行“创建表作为选择”，并将新表替换为旧表。如果你能负担得起，插入/*+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循环遍历表格，并发出一个限制行数的删除语句。如果针对正在运行删除操作的表存在重要的插入/更新/删除并发负载，则最有可能适用此方法。

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;

- Adam Musch

---

感谢提供的选项。**(1)** 我不会删除太多行（如上所述，<1%），因此复制表可能是愚蠢的。**(2)** 你的第二个选项看起来很好，因为一个ID应该只在一个表中，但我不清楚如何使用它。PURGE_IDS从哪里来？它看起来像是一个单独的表，而不是TABLE1_PURGE，但是为什么要创建一个新表，当它们已经在一个新表（PURGE_IDS）中了呢？**(3)** 如我的评论中所述，在此过程中不会写入任何内容。将运行一些SELECT语句。 - GIS-Jonathan

我假设有一些业务规则，您可以在其中捕获用于从表集中删除行的ID。创建TABLE1_PURGE的“优势”是，在删除它们之前，它将捕获从TABLE_1中删除的ID，因此您可以从未来的清除中消除这些ID。 - Adam Musch

我刚刚测试了一下，似乎我没有完全理解它在做什么（因此有评论问题）。你指定的方法2非常快！在20万行表格上只用了不到3秒钟！对于一个300万行的表格（相同的7000个删除），只需要36秒！与尝试过的所有其他方法相比，这是一个巨大的速度提升，我没有意识到有这么大的变化。非常感谢！ - GIS-Jonathan

我对第二个选项有点困惑。有4个语句，一个创建，2个删除和一个删除表。您是为整个操作执行一次创建和删除，然后根据需要执行n次删除，还是将它们全部执行n次？ - sisharp

您需要为想要删除的每个表重复这四个语句。因此，在从TABLE2清除时，您需要创建TABLE2_PURGE表，依此类推。 - Adam Musch

Answer 2

将所有要删除的ID存储到一个表中。然后有三种方式。 1）循环遍历表中的所有ID，然后每次删除一行，以X提交间隔为单位。 X可以是100或1000。 它适用于OLTP环境，并且您可以控制锁。

2）使用Oracle Bulk Delete

3）使用相关的删除查询。

单个查询通常比多个查询更快，因为上下文切换更少，可能解析更少。

- Richard Chung

Answer 3

首先，在删除期间禁用索引会很有帮助。

尝试使用MERGE INTO语句：
1）创建一个带有ID和来自TABLE1的附加列的临时表，并使用以下测试

MERGE INTO table1 src
USING (SELECT id,col1
         FROM test_merge_delete) tgt
ON (src.id = tgt.id)
WHEN MATCHED THEN
  UPDATE
     SET src.col1 = tgt.col1
  DELETE
   WHERE src.id = tgt.id

- mcha

---

禁用索引在删除期间听起来很不错 - 除非它被用来强制实施唯一性。 - Adam Musch

如果索引位于where子句中指定的列上，则删除索引将是适得其反的。 - Allan

@AdamMusch 索引没有强制唯一性（虽然它被设置为 UNIQUE）。创建它的原因是因为我的 DBA 说如果没有它，Oracle 将为每个 WHERE 子句执行完整的表扫描；正如 @Allan 推测的那样，这个索引与 where 中使用的列相同。还有两个其他的索引，但这两个索引都是应用程序所必需的。 - GIS-Jonathan

小心重建索引，可能会非常痛苦。 - CapelliC

@chac - 重建索引实际上是必要的吗？我的一般阅读材料表明，在数据更改后，索引会自动更新。我发现了这篇Oracle博客文章，但对我来说并没有太多意义（这可能应该是一个单独的问题）。 - GIS-Jonathan

如果统计数据是代表性的，那么当处理大量行时，Oracle会自动不使用索引（这是有意义的情况）。 - David Balažic

Answer 4

我已经尝试了这段代码，它在我的情况下运行良好。

DELETE FROM NG_USR_0_CLIENT_GRID_NEW WHERE rowid IN
( SELECT rowid FROM
  (
      SELECT wi_name, relationship, ROW_NUMBER() OVER (ORDER BY rowid DESC) RN
      FROM NG_USR_0_CLIENT_GRID_NEW
      WHERE wi_name = 'NB-0000001385-Process'
  )
  WHERE RN=2
);

- jatin Goyal