commit or not commit inside a cursor loop

From: Andrew <andyho99_at_yahoo.com>
Date: 28 May 2003 09:12:57 -0700
Message-ID: <8882aa3c.0305280812.701cb4ed@posting.google.com>

Hi,

I need to update a table with couple million rows. For some reason, I have to use pl/sql instead of SQL to do the job. My understanding is that I put COMMIT after the loop if rollback segment is big enough. If I worry the rollback segment, I'll commit every 20,000 rows inside the loop (and after the loop of course).

However, I read pl/sql document the other day. It says,

---

DECLARE
CURSOR c1 IS SELECT ename, job, rowid FROM emp;

my_ename emp.ename%TYPE;
my_job emp.job%TYPE;
my_rowid UROWID;

BEGIN
OPEN c1;
LOOP
FETCH c1 INTO my_ename, my_job, my_rowid; EXIT WHEN c1%NOTFOUND;
UPDATE emp SET sal = sal * 1.05 WHERE rowid = my_rowid; -- this mimics WHERE CURRENT OF c1
COMMIT;
END LOOP;
CLOSE c1;
END;
Be careful. In the last example, the fetched rows are not locked because no FOR
UPDATE clause is used. So, other users might unintentionally overwrite your
changes. Also, the cursor must have a read-consistent view of the data, so rollback
segments used in the update are not released until the cursor is closed. This can slow down processing when many rows are updated.

---

Does this mean that I should NOT put commit statement inside the loop? What's the best practice? Thanks. Received on Wed May 28 2003 - 11:12:57 CDT