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.

cheers

Niall

On 8/16/07, stv <stvsmth_at_gmail.com> 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 9.2.0.8 on Linux x86_64. Perhaps this made
> sense in an older version?
>
> On 8/15/07, stv <stvsmth_at_gmail.com> 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;
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

--
http://www.freelists.org/webpage/oracle-l