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).
