Re: for d in c loop; insert; commit ...

Version 6 eh, with the transaction processing option, those were the days.

Well actually they probably weren't. The hack is there usually in a spurious
attempt to avoid ORA-1555, ORA-1555 actually means that you don't have
enough undo space allocated. Nowadays the obvious 'fix' add more undo space
is pretty reasonable, and to tell the truth it has been for a long time.
There were 2 factors in those days that made it *less* reasonable and the
hack above pretty standard.

   1. People didn't have *that* much disk space around. My first Oracle
   server had 6 disks if I remember correctly, they were 2gb each. 2 were a
   mirrored pair for the OS (SCO) and the rest were a RAID 5 array. This cost a
   *lot* of cash. Datafiles were limited to 2gb in size as well (I seem
   to recall we used 1gb and 500m files. Buying more disk was a big thing.
   2. MAXEXTENTS UNLIMITED hadn't been invented yet. We had a 2k block
   size (as I guess a lot of people did) and so a single segment, including a
   rollback (undo) segment, could have a maximum of 121 extents. So you might
   run out of extents before you hit ORA-1555. You could do things like size
   rollback segments differently and use SET TRANSACTION USE ROLLBACK SEGMENT
   BLAH (or some similar syntax with the same meaning).

Ironically of course doing the above increases the likelihood of ORA-1555,
but the physical constraints imposed by the hardware and the practical
constraints imposed by the software were real enough.

So, I don't in 2007 buy that what we were doing circa 1997 made sense even
then, but the logic was something like the above.



On 8/16/07, stv <stvsmth@xxxxxxxxx> wrote:

> Ooops. Forgot to mention this is an old database (probably started
> with version 6) with some major work done when they moved to 8i on
> Solaris. Currently running on Linux x86_64. Perhaps this made
> sense in an older version?
> On 8/15/07, stv <stvsmth@xxxxxxxxx> wrote:
> > Why in the world did I inherit a database with all this nonsense
> everywhere ...
> >
> >         lc := 0;
> >         For d in C loop
> >                 lc := lc + 1;
> >                 # insert stuff
> >                 if mod(lc, 50) = 0 then commit; end if;
> >         end loop;
> >         commit;
> --

Niall Litchfield
Oracle DBA

Other related posts: