Re: Oracle skip numbers

"vasilis" <vasilisk_at_ics.forth.gr> wrote in message news:formr8$1kg9$1_at_ulysses.noc.ntua.gr...
> Hello,
>
> When using Powerbuilder with Oracle, in some cases oracle skips numbers.
> What I mean is that when the ID column has a value of 100, instead of
> getting the next value(101) I get 102 or 103 anything but not the next. It
> updates the same row from 100 to 103 and of course as you might imagine I
> can not retrieve the row of 100. How this problem may be solved(Not
> updating the id column to a different value).
>
> Thanks in advance.
>

Why do you care? It is a column that is a surrogate key, it is a meaningless number.
There are several reasons.
1. If some other session did an insert then a rollback the number would "skip".
2. If the sequence cache gets aged out of the shared pool then it would "skip".
3. If you shut down and restart the db then the cached values will be "lost".

You can solve it, but the scalability of the application will go down the tubes.

Again, why do you care? You shouldn't be relying on it not skipping. Jim Received on Tue Feb 12 2008 - 04:10:13 CST