Re: View current seq. no.

If there's a globally cached 'next' number, what is the point of setting chache to > 0? since there would always be contention for the 'next' value. I suspect that the globally cached next number is used to allocate a new set of values to a new sequence user. (The reason I suspect this is that we have built our own caching sequence engine using stored functions, and this is how it works). Thus if cache is set to 10, and global_next is 4, the first user will be allocated the sequence (4,..,13) and global_next will be bumped to 14, second user will get (14,...,23) and global_next gets bumped to 24. Until the first user runs out of id's, there will be no contention for the global_next as the range already granted is wholly 'owned' by the first user. You can get around the problem of gaps by re-allocating partially used ranges where the connection has dropped to new users, (each range keeps it's own next value).
I may be wrong, perhaps Oracle sequences do not work this way - but I'd like to find out either way :)

Regards, David.

Jonathan Lewis wrote:

> Your comment about each session having its
> own 'current' value (i.e. the most recent value
> it got using the nextval call) is correct, but
> your scenario of A and B alternating is not
> correct:
>
> > If user A and user B use the sequence, first A, then B,
> > then A and then B again and so on ... having cache 10
> > (as an example) defined, you would have
> >
> > A:1 B:11 A:2 B:12 A:3
> >
>
> The sequence would be:
> A:1 B:2 A:3 B:4 A:5
>
> There is a globally cached 'next value' for the sequence
> which is passed to whichever session asks for it.
>
> --
>
> Jonathan Lewis
> Yet another Oracle-related web site: www.jlcomp.demon.co.uk
>
> Dante wrote in message <7h66vj$nla$1_at_nnrp1.deja.com>...
> > It is important to understand that you have
> > a current value for each session using the
> > sequence and a current value for the DB instance.
> >
Received on Thu May 13 1999 - 19:49:43 CDT