Re: intermittent commit on insert ?

From: Norbert Vossiek <norbert.vossiek_at_gmx.li>
Date: Wed, 5 Jun 2002 11:52:21 +0200
Message-ID: <adkn94$r20$03$1@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 - 04:52:21 CDT