Sybrand Bakker wrote:
> On 8 Jul 2005 13:23:06 -0700, "Kevin Blount" <kevin_at_questionmark.com>
> wrote:
>
>
>>This may have aked before, but my searching for such threads didn't get
>>the help I need, so here we go (again):
>>
>>I have a sequence and trigger that allow me to automatically assign an
>>"ID" to a table record. The sequence has the following properties (let
>>me know if I missed anything out)
>>
>>minimum = 1
>>maximum = 1.0E28
>>interval = 1
>>last number = 281
>>
>>cycle values = unselected
>>order values = unselected
>>Cache = default
>>
>>the problem is that the IDs created tend to a) end in 1 and b) jump 20
>>numbers.
>>
>>if I were to insert a new record on two different days, the changes are
>>that the IDs would be 301 and 321 (based on the last number above).
>>However, if I were to add them both at the same time, or within a small
>>time period) they would probably be 301 and 302.
>>
>>My DB admin has spotted the issue, but doesn't know the cause or the
>>fix.
>>
>>Can anyone help? I doesn't really effect any of the scripts I'm
>>writing, but when those IDs are visible it make my applications look
>>wierd, when 90% of the assigned IDs end in 1, ya dig?
>>
>>thanks for reading.. many thanks for replying :)
>
>
> Sequences are primarily intended for surrogate keys. Which means the
> number really doesn't have any meaning at all.
> That said, sequences are not guaranteed to be assigned without holes.
> Ie, if you select a sequence value, but roll back the transaction, the
> sequence isn't lowered, so you 'loose' a sequence number.
> This is of course documented.
> If you want sequences to be ordered, you need to define them ordered.
> The default is noorder, as documented.
> If you want to 'loose' less sequences numbers, you need to make sure
> you define them nocache. By default 20 values are cached, as
> documented. You can define them with nocache, evidently with some
> performance penalty associated (because Oracle would have to read and
> update it's internal sequence table every time).
>
> In short, if you don't want 'holes', don't use sequences.
> And please read the documentation before posting.
>
>
> --
> Sybrand Bakker, Senior Oracle DBA
The fact that his DBA doesn't know all this is what struck me as a concern.
--
Ed Prochak
running http://www.faqs.org/faqs/running-faq/
netiquette http://www.psg.com/emily.html
--
"Two roads diverged in a wood and I
I took the one less travelled by
and that has made all the difference."
robert frost
Received on Fri Jul 08 2005 - 22:35:31 CDT
