PL/SQL Control Structure - LOOP

Question

I have about 94,000 records that need to be deleted, but I have been told not to delete all at once because it will slow the performance due to the delete trigger. What would be the best solution to accomplish this? I was thinking of an additional loop after the commit of 1000, but not too sure how to implement or know if that will reduce performance even more.

DECLARE  
  CURSOR CLEAN IS
  SELECT EMP_ID, ACCT_ID FROM RECORDS_TO_DELETE F; --Table contains the records that needs to be deleted.
  COUNTER INTEGER := 0;

BEGIN
  FOR F IN CLEAN LOOP
    COUNTER := COUNTER + 1;
    DELETE FROM EMPLOYEES
    WHERE EMP_ID = F.EMP_ID AND ACCT_ID = F.ACCT_ID;         

    IF MOD(COUNTER, 1000) = 0 THEN     
      COMMIT;
    END IF;
  END LOOP;
  COMMIT;
END;

Igor Konoplyanko · Accepted Answer · 2016-03-16 14:19:51Z

1

You need to read a bit about BULK COLLECT statements in oracle. This is commonly considered as proper way working with large tables.

Example:

    LOOP
        FETCH c_delete BULK COLLECT INTO t_delete LIMIT l_delete_buffer;

        FORALL i IN 1..t_delete.COUNT
            DELETE ps_al_chk_memo
             WHERE ROWID = t_delete (i);

            COMMIT;
        EXIT WHEN c_delete%NOTFOUND;
        COMMIT;
    END LOOP;
    CLOSE c_delete;

answered Mar 16, 2016 at 14:19

Igor Konoplyanko

9,4046 gold badges62 silver badges107 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

mahi_0707 Over a year ago

Don't you think executing Commit after each record deletion will impact the performance? That's ~94k commit transactions. Won't single commit be sufficient in this case?

Igor Konoplyanko Over a year ago

Point here is that every chunk is limited with l_delete_buffer, so at least whole database will be not locked. When you will have commit only once on whole database - then table will be locked unless all triggers will be processed for all records.

Wernfried Domscheit · Accepted Answer · 2016-03-16 16:08:36Z

1

You can do it in a single statement, this should be the fastest way in any kind:

DELETE FROM EMPLOYEES
WHERE (EMP_ID, ACCT_ID) =ANY (SELECT EMP_ID, ACCT_ID FROM RECORDS_TO_DELETE)

answered Mar 16, 2016 at 16:08

Wernfried Domscheit

60.4k10 gold badges93 silver badges132 bronze badges

1 Comment

Mark Stewart Over a year ago

Usually people who think deleting lots of records at once will take a long time have not tried it. So I agree with do it in one statement. And ask if the delete trigger could be temporarily disabled safely while your statement runs.

Tony Andrews · Accepted Answer · 2016-03-16 17:26:50Z

0

Since I can see the volume of record is not that much so can still go with SQL not by PLSQL.Whenever possible try SQL. I think it should not cause that much performance impact.

DELETE FROM EMPLOYEES
WHERE EXISTS
(SELECT 1 FROM RECORDS_TO_DELETE F
WHERE EMP_ID = F.EMP_ID 
AND ACCT_ID= F.ACCT_ID);

Hope this helps.

edited Mar 16, 2016 at 17:26

Tony Andrews

133k23 gold badges230 silver badges266 bronze badges

answered Mar 16, 2016 at 15:43

Avrajit Roy

3,3131 gold badge16 silver badges25 bronze badges

1 Comment

przemo_pl Over a year ago

if this may really slow down your application's database, can't you just schedule it for execution for let's say midnight? or if this is worldwide application when you have to keep the performance 24h/day, just find some off pick hours... and stick to the SQL whenever possible, really... :)

Collectives™ on Stack Overflow

PL/SQL Control Structure - LOOP

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related