Re: 'SELECT nexval FROM sequence' in a PL/SQL loop: use a cursor ?

"Spendius" <spendius_at_muchomail.com> schrieb im Newsbeitrag news:aba30b75.0310230603.50ef719a_at_posting.google.com...
> Hi,
> I found the following in the code of one of the developers
> here:
> > FOR r_get_tael IN c_get_tael LOOP
> > OPEN c_get_tael_seq;
> > FETCH c_get_tael_seq INTO r_get_tael_seq;
> > CLOSE c_get_tael_seq;
> > BEGIN
> > ...
> which loops 140 000 times. The c_get_tael_seq cursor
> only performs a simple
> > SELECT <seq.>NEXTVAL FROM DUAL;
> so I just would like to know whether the fact he uses
> a cursor (that gets opened and closed 140 000 times) will
> have a bad impact or not compared with the use of the mere
> SQL statement it refers to, i.e. instead of using:
> > FOR r_get_tael IN c_get_tael LOOP
> > SELECT <seq.>NEXTVAL into <variable> FROM DUAL;
> > BEGIN
> > ...
> ??
>
> What do you think ?
> Thanks.
> Regards,
> Spendius

Spendius,
What springs to my mind is:

Why on earth looping for a sequence at all ? The above is just a waste.

A sequence does not start at 1 everytime You use it. User A selects 1 from it
User B selects 2 nextval as from it, because 1 is already used User A then selects 3 from it and then 4 and so on. No sequence number can be fetched twice, that is what sequences are for.

You can check the actual value in user_segments view.

And secondly: yes, opening and closing in a loop (especially for something like that)
is ... erh, no good idea of course.
Anything that is producing overhead consumes time. Maybe not for a loop of 100, but for 140.000 ... my god !

hth, Jan Received on Thu Oct 23 2003 - 09:49:38 CDT