Re: HowTo - create user defined sequence with proper serialization

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

>

>
>

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. Received on Tue Aug 29 2006 - 11:55:51 CDT