Re: Question on sequence number with Dual Table

"John Yang" <zyang_at_waveline.com> wrote in message news:c137b465.0211200434.5a1c2b9e_at_posting.google.com...
> I am a beginner here, so I would really appreciate if you could give
> me some pointers to the following problem.
>
> I am using the Dual table to generate increasing sequence numbers,
> 1,2,3.... But the output numbers seems to jump from time to time, i.e.
> 1,2,3,11,12...
>
> First of all, I don't know if this is the best way of generating
> incremental sequence numbers. Second, I could not understand why the
> sequence number jumps, since there is only one instance calling the
> Dual table, why should it not be sequential? Or is it supposed to be
> sequential?
> I am running on Linux.
>
> Many thanks in advance.
>
> John

Hi John,

This is a very common question so I would recommend a quick search through the google archives for the low down.

To quickly answer your question, sequences are designed to generate *unique* numbers in a sequential manner but without guaranteeing there be no "gaps" in the sequence. Gaps can be caused in numerous ways. One way is how is probably how you are experiencing them, by having values cached in memory (i.e.. a block of numbers are read at a time and accessed for efficiency directly from the memory). In memory, unused numbers can potentially be lost via the simple loss of the memory (i.e.. by shutting down the instance) or by the sequence numbers being "aged out" due to sufficient non use.

If your business rules do not permit the generation of these gaps, then sequences are probably not for you.

Cheers

Richard Received on Wed Nov 20 2002 - 08:02:38 CST