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

What you say Randy can in fact be done but I (personally) would not recommend it, for the simple reason if you are returned an ID of 20 for example there is nothing to stop a (concurrent) user getting the same value. This of course could then lead to a PK violation.

When ever I want to preserve the IDs I always use Procedures to do the inserting (as a transaction), and on my DB at least, in the event of a rollback the ID is still available, for the next insert.

In article <W_ZR4.28$741.12002_at_wdc-read-01.qwest.net>, dwyermj_at_co says...
> 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>
> >
>
>
>
 

-- 
Regards,


William.
 
EuroNet*Internet BV, http://www.euronet.nl/
Wm. G. Urquhart, Oracle DBA
Muiderstraat 1, Postbus 11095, 1001 GB Amsterdam
T:+31 20 5355263, F:+31 20 5355400, E: williamu_at_nl.euro.net
M:+31 06 28206038 ICQ: 56047340