An Oracle blog – Amardeep Sidhu

October 8, 2009

Delete Delete Faster Faster ;)

Filed under: SQL — Sidhu @ 8:53 pm
Tags: ,

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 Comments »

  1. What is the joke here?

    I am DB ignorant of the highest level 🙂

    Comment by Vaibhav — October 10, 2009 @ 7:58 am | Reply

  2. 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 Sidhu — December 21, 2009 @ 9:38 pm | Reply

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

    Comment by maclean — June 9, 2010 @ 10:07 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Theme: Rubric. Get a free blog at WordPress.com

%d bloggers like this: