Re: When to commit;

On Fri, 23 Feb 2001 15:23:53 GMT, Joe Condle <condle_at_mars-systems.com> wrote:

>I have 3,.000,000 plus rows to null out in my database. This will run
>for quite a while if I do it in sqlplus and when I am done I will
>commit. I am afraid of system slowdown unitl the commit is executed. I
>am know going to write a pl/sql program to perform the update so I can
>control how often the update stmt commits. What is a good rule of thumb
>on commits. How many statements can I process before I commit and still
>not hamper system performance.

Your assumption regarding slowdown is false. The only problem you can encounter is running out of rollback space. You should try to avoid writing programs like you describe above. Your best bet is you're going to get the dreaded ora-1555 error, which usually is best avoided by, guess what, not committing inside a loop. IMO, the best sensible advice is to split your transaction in smaller logical chunks, like process all SSN-s starting with a 1 first, followed by all SSN- starting with a 2, and so on. You commit after each subdivision. This might be an ill-choosen example, but you probably get the gist of what I mean.
I wouldn't be afraid of updating 3000000 rows in one go though, it is all an issue of sufficient rollback space.

Hth,

Sybrand Bakker, Oracle DBA Received on Fri Feb 23 2001 - 10:00:49 CST