Re: When to commit

The decision of how often to commit is very specific to what you are doing.

If all 3000 rows are considered a single event, and your database has sufficient rollback space, I would recommend not committing until all of the rows have been successfully inserted. This allows you to rollback the entire run and no restart procedures are needed should something go bad. In this case you may need to contact your DBA to get you a large enough rollback segment to complete the transaction.

On the other hand, if each row represents a separate and distinct event, you might want to consider committing more often. Usually committing after each row in a batch situation represents considerable overhead. So you may decide to commit after every 100, 500, 1000 rows. If you choose this approach, remember that you need some way to know where you are in the insert process when the last commit occurred so you can restart at that point should an error occur.

Hope that helps.

• Ronald K. Olcott, President
• Interlude Enterprises
• Concord, CA
• interld808_at_aol.com

In article <35ED5CAE.4AC59AD3_at_shell.com>,   yong <yong_at_shell.com> wrote:
> My script tries to insert into a table some values. There're about 3000
> rows to be inserted. On one extreme I can commit after each row insert.
> On the other, I can commit after all are inserted. How do I find a
> proper number (approximately) of rows for committing considering
> performance and server memory (or rollback segment size)? Which rollback
> segment should I look at and how do I check its size? Thanks for advice.
>
> I don't know where to find a FAQ for this group. Sorry if this has been
> asked before.
>
> Yong Huang
> Email:yong_at_shell.com
>
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Wed Sep 02 1998 - 13:53:27 CDT