Re: unsequential sequences

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