Re: HowTo - create user defined sequence with proper serialization

From: Brian Peasland <dba_at_nospam.peasland.net>
Date: Tue, 29 Aug 2006 19:18:37 GMT
Message-ID: <J4rxn4.Iqs@igsrsparc2.er.usgs.gov>

p.santos000_at_gmail.com wrote:

> fitzjarrell_at_cox.net wrote:
>> p.santos000_at_gmail.com wrote:
>>> bdbafh_at_gmail.com wrote:
>>>> p.santos..._at_gmail.com wrote:

>>>>> Folks,
>>>>> Here is a difficult question that I'm hoping you can help me solve.
>>>>>
>>>>> Requirements:
>>>>> - Create a user defined like "sequence" that increments by
>>>>> power of 2.
>>>>> - This defined "sequence" must distribute values like
>>>>> 1,2,4,16,256 etc ..
>>>>> - If a value is deleted, then the sequence must re-use that
>>>>> value.
>>>>> - acess to this "sequence" must be serialized so that no two
>>>>> users can
>>>>> grab the same value
>>>>>
>>>>> Example: (GROUPS table)
>>>>>
>>>>> ACCOUNT_ID NAME SEQ
>>>>> ======= ===== =====
>>>>> 347164311 GROUP1 1
>>>>> 347164311 GROUP2 2
>>>>> 347164311 GROUP2 4
>>>>>
>>>>> - So if account id " 347164311" wanted to create a new group in the
>>>>> GROUPS
>>>>> table, it would obtain a new sequence with the value of 16 and it
>>>>> would insert it.
>>>>>
>>>>> - If at some point GROUP2 was removed from the table, the custom
>>>>> sequence
>>>>> would distribute 2 as the "NEXTVAL" ..
>>>>>
>>>>> The key thing here is serialization and I'm not sure how to achive
>>>>> this.
>>>>> Here is the sample function that delivers the next available sequence,
>>>>> but
>>>>> doesn't yet serialize access to it.
>>>>>
>>>>> CREATE OR REPLACE FUNCTION GET_SEQ (ACCTID NUMBER)
>>>>> RETURN NUMBER IS
>>>>> i NUMBER := 0;
>>>>> two_power_i NUMBER := power(2,i);
>>>>> curr_mask groups.seq%TYPE;
>>>>>
>>>>> CURSOR masksCursor IS SELECT seq FROM groups WHERE account_id = acctid
>>>>> ORDER BY seq;
>>>>>
>>>>> BEGIN
>>>>> OPEN masksCursor;
>>>>> LOOP
>>>>> FETCH masksCursor INTO curr_mask;
>>>>> EXIT WHEN curr_mask != two_power_i OR masksCursor %NOTFOUND;
>>>>> i := i + 1;
>>>>> two_power_i := power(2,i);
>>>>> END LOOP;
>>>>> CLOSE masksCursor;
>>>>> RETURN two_power_i;
>>>>> END;
>>>>> /
>>>>>
>>>>> Any idea on how to make the above function serial so that if 2
>>>>> concurrent users execute the function, they don't both get the same
>>>>> value?
>>>>>
>>>>> -peter

>>>> http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/functions102.htm#i78493
>>>>
>>>> Peter,
>>>>
>>>> If what you are indeed after is a sequence of numbers which are 2^^n,
>>>> where n = 0,1,2, ... m
>>>> One could just create a sequence (e.g. myseq) with a starting value of
>>>> 0, increment 1, nocache.
>>>> Create a function to return POWER(2,myseq.nextval) and use that in your
>>>> insert statement. Return myseq.currval if needed.
>>>>
>>>> -bdbafh
>>>
>>> That is an interesting way of doing this, but then I would have to have
>>> a sequence per
>>> account_id because each value returned is by account_id.  Also if my
>>> sequence distributed a nextval of "4" and that group later on gets
>>> removed from the table, there is no way to re-use that value anymore.
>>>
>>> -peter
>> I'm wondering if the 'requirement' to reuse values is yours, or
>> dictated by someone else.  I see no real purpose in it except as an
>> exercise.
>>
>>
>> David Fitzjarrell
> 
> No the requirement is not mine. The sequence value will actually be
> used for bitwise operations.  Basically a process needs to quickly
> identify if a user has permissions to acccess certain components of a
> web page..  The larger the sequence number, the more difficult these
> bitwise operations become , so we want to keep the sequence values as
> low as possible... that's why we want to re-use them when possible.
> 

I'm having a hard time reconciling why you want to use a sequence with the information you gave above. Each bit will represent a "certain component". If the bit is set (1) the user has access to that component. If the bit is not set (0) the user does not have access. Why use a sequential number generator here? Bit0 should correspond to Component0. Bit1 should correspond to Component1. Why use a sequential number generator for this?

Cheers,
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

Received on Tue Aug 29 2006 - 14:18:37 CDT