Re: Missing sequences... how to retrieve the missing no?

This should be in a FAQ.

What you are asking to do should not be done with a sequence. Use select max(id) +1 from table. You really cannot guarantee contiguous (uninterrupted) values from a sequence.

"Randy DeWoolfson" <randy_at_euclidsys.com> wrote in message news:39185143.4B089986_at_euclidsys.com...
> that is not really the point of a sequence - or a primary key... these
> just need to be unique.
>
> You could write code to pack down your number sequence and fill
> in the gaps, and then reset your sequence generator- any number of
> algorithms would work, but why? lots of work for little gain...
>
> randy
>
>
> jess wrote:
>
> > Dear Netizens
> >
> > I am facing the issue of missing sequence number in one of our tables. I
> > had set the sequence number to nocache. Hence, the system is slow and
> > will need to retrieve the number from the disk whenever it is needed.
> >
> > However, individual sequence numbers can be skipped if they were
> > generated and used in a transaction that was ultimately rolled back. How
> > do I make provisions to catch and reuse these sequence numbers?
> >
> > Thanks in advanced.
> >
> > Rgds,
> >
> > Jessica
> > <jesskung_at_brel.com>
>
Received on Tue May 09 2000 - 00:00:00 CDT