Re: commit or not commit inside a cursor loop

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;
So this process gets half way through and the server dies or you get a 1555 or space allocation error or something like that. Now half the people already got their raises, the other half didn't, and you have no reliable way to know who did or who didn't. Isn't that cheerful?

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

I don't get this. I suppose other users *might* unintentionally overwrite your changes here, but they might do that anywhere, anytime. What about this makes that a particular hazard?

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

Do not put a commit inside the loop if you can possibly get away from it.

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service              New Rate! $9.95/Month 50GB