Delete Delete Faster Faster ;)

2-3 days ago, I came across a code, intended to make delete faster. Just have a look ;)

[sql]. . . LOOP SELECT COUNT (1) INTO v_cnt FROM table1 WHERE ROWNUM < 2;

IF v_cnt = 0 THEN EXIT; END IF;

DELETE FROM table1 WHERE ROWNUM < 1000;

COMMIT; v_cnt := 0; END LOOP; . . .[/sql]

Comments

Comment by Vaibhav on 2009-10-10 07:58:17 +0530

What is the joke here?

I am DB ignorant of the highest level 🙂

Comment by Sidhu on 2009-12-21 21:38:36 +0530

Hey man…

Sorry for the prompt reply 😛 .

The Joke is that he is committing inside the LOOP…which is a disaster in a database.

Comment by maclean on 2010-06-09 22:07:19 +0530

how many rows stored in this table?non-commit loop may cause large undo tablespace or ora-1555, i think we’d better choose a good recurring number and then commit.