Re: OPS Sequences: nocache == order ??

Ok,

It is saturday morning (brain is working at half power), I have looked at this function and at the original requirement and see a problem (may be 2).

1. The time of the multiple instances needs to be in sync with each other. If not, it could be that the 2nd instance has an earlier time and insert a record with a lower number after an insert of a higher number. So the real order is lost (that was a requirement).
2. Given the fact that the sequence numbers may be cached, even when the time is in sync, depending on the cached sequence numbers you could still end up with one instance inserting a number with a higher sequence number before the other instance with a lower sequence number in the same time (at seconds level).

So if the requirements aren't so strict, why not drop the 'no order' and bump the cache ?

Again, I may have missed something.

Anjo.

On Wednesday 04 September 2002 08:28, you wrote:
> Mladen,
>
> Is there any way to have developers/users access the sequence via a
> function, instead of accessing the sequence directly?
>
> If so, then perhaps you could modify the sequence to add the temporal
> component, while maintaining the use of a cached sequence for uniqueness?
> Such as: SQL> create or replace function gen_seqq(in_seq in number)
> 2 return number
> 3 as
> 4 v_return_nbr number;
> 5 begin
> 6 select
> to_number(to_char(sysdate,'YYYYMMDDHH24MISS')||ltrim(to_char(in_seq,'000000
>000000'))) 7 into v_return_nbr
> 8 from dual;
> 9 return v_return_nbr;
> 10* end gen_seqq;
> SQL> /
>
> Function created.
>
> SQL> create table x (y number);
>
> Table created.
>
> SQL> create sequence xq;
>
> Sequence created.
>
> SQL> insert into x values (gen_seqq(xq.nextval));
>
> 1 row created.
>
> SQL>
> Big and ugly numbers yes, but I think some folks get a strange thrill out
> of 20-digit numbers.
>
> It fits the requirement of being temporal (to the second, at least) and
> unique. You can throw in HSECS from V$TIMER if someone gets picky enough
> to want to go to the centi-second level as well. Yeah, and you can throw
> in USERENV('INSTANCEID') too, just for some real OPS/RAC-ness! Best of
> all, it fits the DBA-half of your brain by being fully cacheable and
> non-pinging...
>
> ...of course, you can embed the use of the SEQUENCE object inside the
> function; I left it on the "outside" in this example just to make it more
> flexible with regard to which sequence object it uses...
>
> If they don't like the idea of using a stored function to get the sequence
> number, then tell 'em that "it's more ANSI standard that way" and it's
> "database independent". That gets 'em every time...
>
> Hope this helps...
>
> -Tim
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Tuesday, September 03, 2002 5:54 PM
>
> > Unfortunately, we have an application dependency and I was required
> > to come up with a quick & dirty fix. Thanks for your reply.
> >
> > On 2002.09.03 19:10 Anjo Kolk wrote:
> > > If you run OPS and specify order, it works like no cache.
> > >
> > > My question to you: "Why cripple OPS and your business performance by
> > > having this requirement ?" Spending a few bucks to get rid of this
> > > dependency will improve the performance, until you run in to the next
> > > problem ;-)
> > >
> > > Anjo.
> > >
> > > On Wednesday 04 September 2002 00:00, you wrote:
> > > > I'm managing an OPS configuration (4x HP 9000/N, HP-UX 11/64 , RDBMS
> > > > 8.1.7.1)
> > > > and I'm having an application dependency on a temporal order of
> > > > sequence numbers.
> > > > With OPS that becomes a problem because each node caches a set of
> > > > sequence numbers
> > > > (20 by default). Oracle has an option, specifically for that
> > > > situation, namely "ORDER".
> > > > My question is whether ORDER is the same thing as NOCACHE and whether
> > > > it is possible
> > > > to have a NOCACHE sequence which will return numbers in an incorrect
> > > > order (larger number
> > > > before the smaller one).
> > > > Please, o OPS gods and godesses, help me out and I'll sacrifice you a
> > > > beer when I see you.
> > > > Mladen Gogala
> > >
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > > --
> > > Author: Anjo Kolk
> > > INET: anjo_at_oraperf.com
> > >
> > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > > San Diego, California -- Public Internet access / Mailing Lists
> > > --------------------------------------------------------------------
> > > To REMOVE yourself from this mailing list, send an E-Mail message
> > > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > > the message BODY, include a line containing: UNSUB ORACLE-L
> > > (or the name of mailing list you want to be removed from). You may
> > > also send the HELP command for other information (like subscribing).
> >
> > --
> > Mladen Gogala
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Mladen Gogala
> > INET: mgogala_at_adelphia.net
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Anjo Kolk
  INET: anjo_at_oraperf.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).