Re: intermittent commit on insert ?

It's interesting....this thread almost qualifies for the "Oracle myths" discussion a while back...

I just opened one of my PL/SQL references and confirmed what I thought it said - here it is :

Sql tips & techniques - "Use a counter to limit transaction size" "...it is possible and desireable to commit transactions at certain time intervals throughout the processing.."

"TIP: Implement the use of a counter to perform a COMMIT for a specified number of transactions..this technique is most commonly implemented within loops, particularly those based on cursors" etc...then goes on with sample code...

Also, a question on the authors' wording....isn't a 'transaction' DEFINED by a commit (or rollback)? how can you code a commit for a "specified number of transactions"?

Anyway boys and girls....I've learned not to mimic that author's "tips" :)

thanks again

steve
"Niall Litchfield" <niall.litchfield_at_dial.pipex.com> wrote in message news:3cfd30be$0$8506$cc9e4d1f_at_news.dial.pipex.com...
> "Chuck" <chuckhNOSPAM_at_softhome.net> wrote in message
> news:adj4tq$116qbh$1_at_ID-85580.news.dfncis.de...
> > Why would smaller commits increase the log volume? You're still
inserting
> > the same amount of data into the table. The miniscule amount of log
space
> to
> > hold the commit records isn't even worth mentioning.
>
> No-one said it would change the log volume. Redo isn't the issue here.
Undo
> is.
>
> >
> > I think everyone is missing the point here though - how much RBS space
is
> > consumed by inserts? It's tiny! The space required to insert 1m rows is
> > problably around 10m. If you're RBS can't afford a 10m RBS you've got
some
> > serious space issues.
>
> The point is that the rollback system will suffer under artificially high
> numbers of transactions. You commit at the end of a transaction and the
> DBA's job (pain that it is) is to size rollback segments appropriately.
>
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
>
>
Received on Wed Jun 05 2002 - 10:27:14 CDT