Re: Sequence Table

"Jason Webber" <webber_at_mminternet_spam.com> wrote:

>I need a way to have a sequence for a given amount of mobile assets for a
>messaging system. Each message gets assigned a sequence number. Each
>mobile asset needs to have its own sequence to get the next number from. I
>don't think creating a sequence object for each mobile asset sounds
>appropriate in this case since I would have to create a new sequence each
>time I add a mobile asset. Right now there could very easily be 500,000 of
>these mobile assets.

Jason, the problem IMO is that you are not really dealing with Oracle sequences (i.e. generated "random" numbers) when each mobile assets need its own unique sequence for messages. And that is the reason why Oracle sequences do not fit the job IMO.

If a number has meaning in the business sense, then it is not an Oracle sequence. Oracle sequences are nothing but a unique random number generator (okay, not entirely random as you can predict the range in which the nextval sequence will fall in).

So in that respect, Oracle sequences are totally meaningless IMO. It's only job is to supply you with a unique meaningless random number. Nothing more.

>Is there a better way to do this so that two threads
>in my application that are running and try to get the next sequence
>number don't end up with the same number.

An Oracle sequence guarantees a unique number for each nextval call, irrespective of the number of simultanous nextval calls (from the same app threads, or different apps all together).

I would re-visit the business specs and requirements then re-think the database design.

If push comes to shove and Oracle sequences are to be the dirty way to do it, I would consider using a PL/SQL wrapper function that uses dynamic SQL to call nextval using the asset number to determine what sequence to use. At least then you have isolated the problem to a single function call - which should be easier to manage and maintain.

--
Billy