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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Tim Gorman
INET: Tim_at_SageLogix.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 - 01:28:24 CDT
