Re: Oracle skip numbers

Thanks for your response,

I care about it because when I retrieve the row I use the first value inserted not the updated one which might be or might be not the nextval on the same cell. Since you say I can solve it do you know how?

Thanks you
"news.verizon.net" <kennedyii_at_verizon.net> wrote in message news:9gesj.5708$qw4.2994_at_trnddc02...
>
> "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 - 05:07:09 CST