Delete Delete Faster Faster ;)

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

.
.
.
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;
.
.
.

3 thoughts on “Delete Delete Faster Faster ;)

  1. Sidhu Post author

    Hey man…

    Sorry for the prompt reply 😛 .

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

    Reply
  2. maclean

    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.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *