在Oracle中删除大量数据 [英] Deleting a LOT of data in Oracle

问题描述

确切地说，我不是数据库人员，我的大部分数据库工作都是在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语句并分批提交200,000条还是拥有一条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.

想法?最佳做法?

编辑:我运行了第一个选项，即单个delete语句，并且仅用了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.

这篇关于在Oracle中删除大量数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！