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 -----
Sent: Tuesday, September 03, 2002 5:54
PM
Subject: 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).
