使用PL/SQL删除大量记录 [英] Deleting a large number of records using PL/SQL

问题描述

我想使用PL/SQL删除大量记录.记录由DATE字段标识，该字段标识上次修改记录的时间.我不想消耗太多的资源，所以我认为应该限制要删除的记录的数量，在我看来，伪列ROWNUM可以满足此目的.然后，我检查更新影响的行数，并重复直到影响的行数为0.

I want to delete a large number of records using PL/SQL. The records are identified by a DATE field that identifies the last time the record was modified. I don't want to consume too many resources, so I thought that I should limit the number of records that were being deleted and it seemed to me that the pseudo column ROWNUM could server that purpose. I then check the number of rows effected by the update and repeat until the number of rows effected is 0.

我正在寻找有关最佳做法的建议.我也很担心收到的警告:

I'm looking for a recommendation for the best practice in doing this. I am also concerned about the warning that I am getting:

包含DML语句的循环应重构为使用BULK COLLECT和FORALL."

"A loop that contains DML statements should be refactored to use BULK COLLECT and FORALL."

但是当我在主题上搜索Google时，这似乎不适用于我正在尝试执行的操作-还是这样做?

But when I google the topic, it seems like this doesn't apply to what I am trying to do-or does it?

欢迎您提出意见和建议.

Your comments and recommendations are welcomed.

CREATE OR REPLACE PACKAGE BODY MY_PURGE
AS
   PROCEDURE PURGE_MY_TABLE (v_Cut_Off_Date   IN     DATE,
                                 C_MAX_DELETE     IN NUMBER,
                                 DEL_COUNT        OUT NUMBER)
   IS
      v_RECORDS_DELETED   NUMBER := 0;
      V_DONE              BOOLEAN := FALSE;
   BEGIN
      DEL_COUNT := 0;

      WHILE NOT V_DONE
      LOOP
         DELETE FROM MYTABLE
               WHERE     UPDT_TIMESTMP < v_Cut_Off_Date
                     AND ROWNUM <= C_MAX_DELETE;

         v_RECORDS_DELETED := SQL%ROWCOUNT;
         DEL_COUNT := DEL_COUNT + v_RECORDS_DELETED;

         IF (v_RECORDS_DELETED = 0)
         THEN
            V_DONE := TRUE;
         END IF;

         COMMIT;
      END LOOP;
   END;

谢谢

推荐答案

您关心消耗哪些资源?单个DELETE语句将是最有效的方法*.假设需要定期执行此操作，则实际上应该根据UNDO表空间调整数据库的大小，以允许您执行单个DELETE.

What resources are you concerned about consuming? A single DELETE statement is going to be the most efficient approach*. Assuming this is something that needs to be done regularly, the database should really be sized appropriately in terms of UNDO tablespace to allow you to do a single DELETE.

实际上，退后一步，最有效的方法是按UPDT_TIMESTMP对表进行分区，然后删除较旧的分区.但是，分区是在企业版许可证之上的一个额外成本选项，对表进行分区可能会对系统产生其他影响.

Actually, taking a step back, the most efficient approach would be to partition the table by the UPDT_TIMESTMP and drop the older partition(s). But partitioning is an extra cost option on top of your enterprise edition license and partitioning the table may have other impacts on the system.

如果确实需要通过临时提交批量删除行，那么这似乎是一个非常合理的实现.我真的只会考虑如果单个DELETE语句占用了我每晚处理窗口的很大一部分，并且我担心DELETE在几个小时后会强制回滚并重新启动整个过程而失败.批量删除将比正常情况下单个DELETE慢，但是重新启动会更容易.

If you really, really need to delete rows in batches with interim commits, this appears to be a pretty reasonable implementation. I would really only consider this if the single DELETE statement took a substantial fraction of my nightly processing window and I was concerned that the DELETE might fail after a couple hours forcing a rollback and a restart of the entire process. Deleting in batches would be slower than doing the single DELETE normally but it would be easier to restart.

在这种特殊情况下，使用BULK COLLECT和FORALL的建议没有意义.这将适用于更常见的情况，即有人从一个或多个源表中选择数据，在PL/SQL中进行一些处理，然后将数据写到目标表中.通过批量操作而不是通过缓慢的逐行处理来做到这一点将更加有效.但是，将其作为单个INSERT ... SELECT进行操作会更加有效.

The recommendation to use BULK COLLECT and FORALL doesn't make sense in this particular case. It would apply to the more common case where someone is selecting data from one or more source tables, doing some processing in PL/SQL, and then writing the data out to a destination table. It would be more efficient to do that via bulk operations rather than via slow row-by-row processing. But it would be even more efficient to do it as a single INSERT ... SELECT.

这篇关于使用PL/SQL删除大量记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！