Re: for d in c loop; insert; commit ...
- From: "Niall Litchfield" <niall.litchfield@xxxxxxxxx>
- To: stvsmth@xxxxxxxxx
- Date: Thu, 16 Aug 2007 09:25:04 +0100
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@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 9.2.0.8 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;
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
Niall Litchfield
Oracle DBA
http://www.orawin.info
Other related posts:
