Re: intermittent commit on insert ?

This is exactly what I wanted to do

        > "procedural approach instead of an approach related to set theory"

and at the same time as Chuck says

        > "I would not want to be in the position of having to explain to my boss why I'm tying up gigabytes of space for a rollback segment that gets used once in a blue moon. "

    So I am not able to use a single sql stmt but have to rely on a procedural approach.

    Regarding the > "*logical* transaction" issue with intermittent commits, I think it is ok as long as you are aware of it. ( sometimes things don't happen 'by the book' in the real world)

Sunil.

"Norbert Vossiek" <norbert.vossiek_at_gmx.li> wrote in message news:adkn94$r20$03$1_at_news.t-online.com...
> Sybrand,
>
> > Think about it. THINK.
>
> Why so bitter? Well. I'll give it a try.
>
> Thomas Kyte about "Bad Transaction Habits" (Chapter 4, expert one-on-one):
> "If you can do it in a single SQL statement, do it that way, as it is
almost
> certainly faster."
>
> Yes, I agree. I know, understand and appreciate the arguments of Thomas
> Kyte.
>
> > If you have a *logical* transaction consisting
> > of 1 million records, what is the effect if you split it up into
> > smaller ones?
>
> And just repeating my question, is not what I would call a valuable
> contribution. See below my times of the Thomas Kyte's example (very slow
> machine, and it's german env, but I think you'll get the bottom line). And
I
> added one version with commits every 1000 records and one version with
inner
> commits commented out.
>
> Times:
>
> 10 sec (plain vanilla update)
> 15 sec (loop, commit every 100)
> 15 sec (loop, commit every 1000)
> 14 sec (loop, no inner commit)
>
> What do the results show? I think a significant drawback is the result of
a
> procedural approach instead of an approach related to set theory. This
> row-wise processing contributes some 80% of the overall deceleration.
>
> And, as I stated in my first message, there is NO mentionable difference
> between commiting every 100 or 1000 records.
>
>
> And now the results:
>
>
> SQL*Plus: Release 9.0.1.0.1 - Production on Mi Jun 5 11:20:03 2002
>
> (c) Copyright 2001 Oracle Corporation. All rights reserved.
>
>
> Verbunden mit:
> Oracle8i Release 8.1.7.0.0 - Production
> JServer Release 8.1.7.0.0 - Production
>
> SQL> set timing on
> SQL> update t set object_name = lower(object_name);
>
> 14570 Zeilen wurden aktualisiert.
>
> Abgelaufen: 00:00:10.01
> SQL>
>
> SQL> begin
> 2 for x in (select rowid rid, object_name, rownum r from t)
> 3 loop
> 4 update t
> 5 set object_name = lower(x.object_name)
> 6 where rowid = x.rid;
> 7 if (mod(x.r,100) = 0) then
> 8 commit;
> 9 end if;
> 10 end loop;
> 11 commit;
> 12 end;
> 13 /
>
> PL/SQL-Prozedur wurde erfolgreich abgeschlossen.
>
> Abgelaufen: 00:00:15.00
> SQL>
>
>
> SQL> begin
> 2 for x in (select rowid rid, object_name, rownum r from t)
> 3 loop
> 4 update t
> 5 set object_name = lower(x.object_name)
> 6 where rowid = x.rid;
> 7 if (mod(x.r,1000) = 0) then
> 8 commit;
> 9 end if;
> 10 end loop;
> 11 commit;
> 12 end;
> 13 /
>
> PL/SQL-Prozedur wurde erfolgreich abgeschlossen.
>
> Abgelaufen: 00:00:15.03
> SQL>
>
> SQL> begin
> 2 for x in (select rowid rid, object_name, rownum r from t)
> 3 loop
> 4 update t
> 5 set object_name = lower(x.object_name)
> 6 where rowid = x.rid;
> 7 -- if (mod(x.r,100) = 0) then
> 8 -- commit;
> 9 -- end if;
> 10 end loop;
> 11 commit;
> 12 end;
> 13 /
>
> PL/SQL-Prozedur wurde erfolgreich abgeschlossen.
>
> Abgelaufen: 00:00:14.01
> SQL>
>
>
>
Received on Wed Jun 05 2002 - 23:30:53 CDT