Re: commit or not commit inside a cursor loop

On 28 May 2003 09:12:57 -0700, andyho99_at_yahoo.com (Andrew) wrote:

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

You should NOT commit inside a loop.
a COMMIT ends a transaction, and releases all locks. Your read consistent view will be released, so the data will be possibly overwritten, resulting in ORA-1555 in your transactions. Just search the google archives and you will find hundreds of posts explaining why you shouldn't do it. Just search on ora-1555 or read the paper on Metalink with respect to ora-1555

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Wed May 28 2003 - 12:35:12 CDT