My first rule for commit "placement" is the respect of
the transaction (hey what do you expect from someone
who likes to normalize before denormalizing ;-) ) then
I looked to improve in terms of performance,
modularity,... so if a transaction is done inside a
loop so be it.
- Jared Still <jkstill_at_cybcon.com> a écrit : >
> I'll have to disagree with not using commit within a
> loop.
>
> If you identify what a transaction is within your
> code, and
> write it accordingly, using commit where
> appropriate, you
> will not get ORA-1002 or ORA-1555, at least not due
> to your own code.
>
> I've written a number of routines to do just that,
> and helped
> developers straighten out their procedures so that
> they
> would quit getting ORA-1555.
>
> Putting commit outside of the loop is simply not
> practical
> for a procedure that is modifying a large amount of
> data,
> and/or takes a long time to do it.
>
> The code below that I originally commented on will
> not
> cause ORA-1555.
>
>
> begin
> loop
>
> update reg.docalert_responses_at_ncp
> set campaign_response_handled = 1
> where campaign_response_handled != 1
> and rownum <= 100
> and docalert_response_id in (
> select docalert_response_id
> from docalert_emails_050401_at_ncc
> where sent = 1
> );
>
> commit;
>
> exit when sql%notfound;
> end loop;
> end;
> /
>
>
>
> Jared
>
>
> On Wednesday 09 May 2001 07:56, paquette stephane
> wrote:
> > Jared,
> >
> > They are many causes for the famous "ORA-1555
> Snapshot
> > too old", one of them is fetch across commit. It
> is
> > when you're commiting and fetching the same data.
> it
> > is not accept in ANSI SQl but it is by Oracle.
> >
> > In numerous place, developpers have complained
> that I
> > sized the rbs too small because of the ORA-1555.
> > Suddenly my rbs were ok after moving the commit
> > outside the loop .
> >
> >
> >
> >
> > --- Jared Still <jkstill_at_cybcon.com> a écrit : >
> >
> > > Stephane,
> > >
> > > This doesn't look like it will cause ORA-1002,
> at
> > > least
> > > I don't see it.
> > >
> > > What's the relationship between ORA-1002 and
> > > ORA-1555?
> > >
> > > I got up rather early with a headache this
> morning,
> > > so maybe I'm
> > > just not thinking clearly yet. :)
> > >
> > > Jared
> > >
> > > On Wednesday 09 May 2001 05:40, paquette
> stephane
> > >
> > > wrote:
> > > > My observation is not on the elegancy of the
> code
> > >
> > > but
> > >
> > > > why commit at 100 rows ? Are you updating 10
> 000
> > >
> > > 000
> > >
> > > > rows ?
> > > > I've seen a lot of ORA-1555 because of fetch
> > >
> > > across
> > >
> > > > commit.
> > > >
> > > >
> > > > --- Diana_Duncan_at_ttpartners.com a écrit : >
> > > >
> > > > > Linda,
> > > > >
> > > > > Might I suggest avoiding the "elegant"
> looping
> > >
> > > and
> > >
> > > > > try some "inelegant"
> > > > > looping? It should be faster, although I
> can't
> > >
> > > make
> > >
> > > > > any promises.
> > > > >
> > > > > Warning, untested, and you can probably do
> > >
> > > better
> > >
> > > > > than an in() -- but it
> > > > > should give you the gist...
> > > > >
> > > > > begin
> > > > > loop
> > > > > update reg.docalert_responses_at_ncp
> > > > > set campaign_response_handled = 1
> > > > > where campaign_response_handled !=
> 1
> > > > > and rownum <= 100
> > > > > and docalert_response_id in
> (select
> > > > > docalert_response_id
> > > > > from
> docalert_emails_050401_at_ncc
> > > > > where sent = 1);
> > > > > commit;
> > > > > exit when sql%notfound;
> > > > > end loop;
> > > > > end;
> > > > > /
> > > > >
> > > > > Diana Duncan
> > > > > TITAN Technology Partners
> > > > > One Copley Parkway, Ste 540
> > > > > Morrisville, NC 27560
> > > > > VM: 919.466.7337 x 316
> > > > > F: 919.466.7427
> > > > > E: Diana_Duncan_at_ttpartners.com
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > "Hagedorn,
> > > > >
> > > > >
> > > > > Linda" To:
> > > > > Multiple recipients of list ORACLE-L
> > > > > <ORACLE-L_at_fatcity.com>
> > > > > <lindah_at_epocr cc:
> > > > >
> > > > >
> > > > > ates.com> Fax
> to:
> > > > >
> > > > >
> > > > > Sent by:
> > >
> > > Subject:
> > > > > Pl/sql loop assistance
> > > > >
> > > > > root_at_fatcity.
> > > > >
> > > > >
> > > > > com
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > 05/08/2001
>
=== message truncated ===
Stéphane Paquette
DBA Oracle, consultant entrepôt de données
Oracle DBA, datawarehouse consultant
stephane_paquette_at_yahoo.com
Do You Yahoo!? -- Pour faire vos courses sur le Net,
Yahoo! Shopping :
http://fr.shopping.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?paquette=20stephane?=
INET: stephane_paquette_at_yahoo.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Thu May 10 2001 - 03:59:07 CDT
