在 Oracle 中删除大量数据

2021-12-24 00:00:00 sql oracle plsql

确切地说，我不是数据库人员，而且我的大部分数据库工作都与 MySQL 相关，所以如果这个问题中的某些内容非常幼稚，请原谅我.

I am not a database person, exactly, and most of my db work has been with MySQL, so forgive me if something in this question is incredibly naïve.

我需要从大约有 1 亿行的 Oracle 表中删除 550 万行.我在临时表中拥有需要删除的行的所有 ID.如果只有几千行，我会这样做:

I need to delete 5.5 million rows from an Oracle table that has about 100 million rows. I have all the IDs of the rows I need to delete in a temporary table. If it were a just a few thousand rows, I'd do this:

DELETE FROM table_name WHERE id IN (SELECT id FROM temp_table);
COMMIT;

有什么我需要注意和/或做不同的事情吗，因为它有 550 万行?我想过做一个循环，像这样:

Is there anything I need to be aware of, and/or do differently, because it's 5.5 million rows? I thought about doing a loop, something like this:

DECLARE
  vCT NUMBER(38) := 0;

BEGIN
  FOR t IN (SELECT id FROM temp_table) LOOP
    DELETE FROM table_name WHERE id = t.id;
    vCT := vCT + 1;
    IF MOD(vCT,200000) = 0 THEN
      COMMIT;
    END IF;
  END LOOP;
  COMMIT;
END;

首先 - 这是否按照我的想法进行 - 一次批处理 200,000 次提交?假设是这样，我仍然不确定是生成550万条SQL语句，然后分批提交20万条，还是一条SQL语句一次全部提交.

First of all - is this doing what I think it is - batching commits of 200,000 at a time? Assuming it is, I'm still not sure if it's better to generate 5.5 million SQL statements, and commit in batches of 200,000, or to have one SQL statement and commit all at once.

想法?最佳做法?

EDIT:我运行了第一个选项，单个删除语句，在开发过程中只用了 2 个小时就完成了.基于此，它排队等待在生产中运行.

EDIT: I ran the first option, the single delete statement, and it only took 2 hours to complete in development. Based on that, it's queued to be run in production.

推荐答案

第一种方法更好，因为您可以让查询优化器清楚地了解您正在尝试做什么，而不是试图隐藏它.数据库引擎在内部删除 5.5m(或表的 5.5%)可能会采用与删除 200k(或 0.2%)不同的方法.

The first approach is better, because you give the query optimizer a clear picture of what you are trying to do, instead of trying to hide it. The database engine might take a different approach to deleting 5.5m (or 5.5% of the table) internally than to deleting 200k (or 0.2%).

这里还有一篇文章，关于 Oracle 中的大量 DELETE，您可能想阅读.

Here is also an article about massive DELETE in Oracle which you might want to read.

相关文章