Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: OPS Sequences: nocache == order ??
-----Original Message-----
From: Tim Gorman [mailto:Tim@SageLogix.com]
Sent: Wednesday, September 04, 2002 2:28 AM
To: Multiple recipients of list ORACLE-L
Subject: Re: OPS Sequences: nocache == order ??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,'000000000000')))
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 -----From: "Mladen Gogala" <mgogala@adelphia.net>To: "Multiple recipients of list ORACLE-L" <ORACLE-L@fatcity.com>Sent: Tuesday, September 03, 2002 5:54 PMSubject: Re: OPS Sequences: nocache == order ??> 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@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@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@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@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: Gogala, Mladen INET: MGogala_at_oxhp.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).Received on Wed Sep 04 2002 - 10:03:24 CDT