Re: ora-1555, order by, x, kyte

From: Niall Litchfield <niall.litchfield_at_dial.pipex.com>
Date: Tue, 6 Apr 2004 21:53:51 +0100
Message-ID: <407318d1$0$6542$cc9e4d1f@news-text.dial.pipex.com>

"Paul" <pkelley_at_coat.com> wrote in message news:473377b4.0404060807.a1e3f5d_at_posting.google.com... > "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:<407277cc$0$3306$ed9e5944_at_reading.news.pipex.net>...
> > "Paul" <pkelley_at_coat.com> wrote in message
> > news:473377b4.0404051228.68ac9624_at_posting.google.com...
> > > Alrighty, then,...
> > >
> > > In late February 2004, "X" posted some notes from metalink re using
> > > "order by" to dodge ora-1555. He was lambasted in this group.
> >
> > I can't see this discussion on Google, is there a link to it?
> >

>
> I searched groups for "oracle snapshot too old sort" without the
> quotes and picked the link for the post from late feb from a thread
> with 39 posts.
>
> This link worked when I tried it a few minutes ago.
>
>

http://groups.google.com/groups?q=oracle+snapshot+too+old+sort&hl=en&lr=&ie= UTF-8&scoring=d&selm=403e6a81%240%2422392%24626a14ce%40news.free.fr&rnum=3

>
>

> > This one I did find, unfortunately I can't tell if the situations are
the
> > same in the two cases. Providing some actual information might help.
> >
> > cheers

>
> The initial posting in oracle.misc included code that fetched across
> commits.   The asktom thread was started by a broad question that
> didn't include a code example.

however the google discussion was started by this example

begin

   FOR c2 in c1 LOOP
 update test_table
 set SSN = TRANSLATE ('915482376','123456789',TRANSLATE(SSN, '1234567890','9732581406'))

        where ssn = c2.ssn;

     counter := counter + 1;

     IF (counter = 2500)
     THEN
        COMMIT;
        counter := 0;
     END IF;

   END LOOP;    COMMIT;
END;
/

to which Tom Kyte's solution 5 (i.e the one before 6) is

5. If fetching across commits, the code can be changed so that this is not done.

or to quote X

But, generally, don't 'fetch across commit', or in normal language don't commit inside loop.

I don't see a big disagreement here...

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************

Received on Tue Apr 06 2004 - 15:53:51 CDT