Friend....you are STILL NOT THINKING....
Norbert Vossiek wrote in message ...
>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 Thu Jun 06 2002 - 16:52:36 CDT
