Re: Oracle-sequence and NOORDER
From: <rop049_at_gmail.com>
Date: Fri, 21 Sep 2012 01:08:20 -0700 (PDT)
Message-ID: <7a989013-6b69-4163-9243-75d940e8571d_at_googlegroups.com>
On Thursday, September 20, 2012 4:07:25 PM UTC+2, ddf wrote:
> On Thursday, September 20, 2012 4:50:02 AM UTC-6, dombrooks wrote:
>
> > > Is it possible to create a test-case where they actually do come out-
>
> >
>
> > > of-order?
>
> >
>
> > If you use RAC and request sequence numbers on different nodes then yes - each node has its own cache.
>
>
>
> Provided that the sequence uses the default of NOORDER (which should be obvious but one can't rely upon that presumption given the original post).
>
>
>
> Creating the sequence ORDER will ensure that all sequence numbers appear in order of the request made to generate them in a RAC/Parallel Server environment [multi-node database] -- as an example:
>
>
>
>
>
> fnorp_seq next value currently 157, sequence created ORDER
>
>
>
> Node 1 (current time):
>
>
>
> insert into plasmatron(frink, storbo, yazschutz)
>
> values ('NALZEEMY', fnorp_seq.nextval, 'PEEFERNEEM');
>
>
>
> fnorp_seq.nextval == 157
>
>
>
> Node 2 (17 seconds later than Node 1 request):
>
>
>
> insert into plasmatron(frink, storbo, yazschutz)
>
> values ('QUEEPI', fnorp_seq.nextval, 'HOSSENGROP');
>
>
>
> fnorp_seq.nextval == 159
>
>
>
> Node 3 (8 seconds later than Node 1 request):
>
>
>
> insert into plasmatron(frink, storbo, yazschutz)
>
> values ('OTOREWP', fnorp_seq.nextval, 'UTTREQA');
>
>
>
> fnorp_seq.nextval == 158
>
>
>
> If the sequence was created NOORDER then Nodes 1, 2, and 3 would create their own cached values:
>
>
>
> fnorp_seq created NOORDER CACHE 20, next generated value will be 93
>
>
>
>
>
> Node 1 (current time):
>
>
>
> insert into plasmatron(frink, storbo, yazschutz)
>
> values ('NALZEEMY', fnorp_seq.nextval, 'PEEFERNEEM');
>
>
>
> fnorp_seq.nextval == 93, isolated cache of 20 sequence values
>
>
>
> Node 2 (17 seconds later than Node 1 request):
>
>
>
> insert into plasmatron(frink, storbo, yazschutz)
>
> values ('QUEEPI', fnorp_seq.nextval, 'HOSSENGROP');
>
>
>
> fnorp_seq.nextval == 133, isolated cache of 20 sequence values
>
>
>
> Node 3 (8 seconds later than Node 1 request):
>
>
>
> insert into plasmatron(frink, storbo, yazschutz)
>
> values ('OTOREWP', fnorp_seq.nextval, 'UTTREQA');
>
>
>
> fnorp_seq.nextval == 113, isolated cache of 20 values
>
>
>
> Note that in this case Node 1 has a sequence cache consisting of values 93 - 112, Node 3 has a cache consisting of values 113 - 132 and Node 2 has a cache consisting of sequence values 133 - 152 -- this is the result of NOORDER. It does not affect the order of the values from the sequence cache, it DOES affect whether each node gets its own cache or works from the global cache.
>
>
>
>
>
> David Fitzjarrell
Date: Fri, 21 Sep 2012 01:08:20 -0700 (PDT)
Message-ID: <7a989013-6b69-4163-9243-75d940e8571d_at_googlegroups.com>
On Thursday, September 20, 2012 4:07:25 PM UTC+2, ddf wrote:
> On Thursday, September 20, 2012 4:50:02 AM UTC-6, dombrooks wrote:
>
> > > Is it possible to create a test-case where they actually do come out-
>
> >
>
> > > of-order?
>
> >
>
> > If you use RAC and request sequence numbers on different nodes then yes - each node has its own cache.
>
>
>
> Provided that the sequence uses the default of NOORDER (which should be obvious but one can't rely upon that presumption given the original post).
>
>
>
> Creating the sequence ORDER will ensure that all sequence numbers appear in order of the request made to generate them in a RAC/Parallel Server environment [multi-node database] -- as an example:
>
>
>
>
>
> fnorp_seq next value currently 157, sequence created ORDER
>
>
>
> Node 1 (current time):
>
>
>
> insert into plasmatron(frink, storbo, yazschutz)
>
> values ('NALZEEMY', fnorp_seq.nextval, 'PEEFERNEEM');
>
>
>
> fnorp_seq.nextval == 157
>
>
>
> Node 2 (17 seconds later than Node 1 request):
>
>
>
> insert into plasmatron(frink, storbo, yazschutz)
>
> values ('QUEEPI', fnorp_seq.nextval, 'HOSSENGROP');
>
>
>
> fnorp_seq.nextval == 159
>
>
>
> Node 3 (8 seconds later than Node 1 request):
>
>
>
> insert into plasmatron(frink, storbo, yazschutz)
>
> values ('OTOREWP', fnorp_seq.nextval, 'UTTREQA');
>
>
>
> fnorp_seq.nextval == 158
>
>
>
> If the sequence was created NOORDER then Nodes 1, 2, and 3 would create their own cached values:
>
>
>
> fnorp_seq created NOORDER CACHE 20, next generated value will be 93
>
>
>
>
>
> Node 1 (current time):
>
>
>
> insert into plasmatron(frink, storbo, yazschutz)
>
> values ('NALZEEMY', fnorp_seq.nextval, 'PEEFERNEEM');
>
>
>
> fnorp_seq.nextval == 93, isolated cache of 20 sequence values
>
>
>
> Node 2 (17 seconds later than Node 1 request):
>
>
>
> insert into plasmatron(frink, storbo, yazschutz)
>
> values ('QUEEPI', fnorp_seq.nextval, 'HOSSENGROP');
>
>
>
> fnorp_seq.nextval == 133, isolated cache of 20 sequence values
>
>
>
> Node 3 (8 seconds later than Node 1 request):
>
>
>
> insert into plasmatron(frink, storbo, yazschutz)
>
> values ('OTOREWP', fnorp_seq.nextval, 'UTTREQA');
>
>
>
> fnorp_seq.nextval == 113, isolated cache of 20 values
>
>
>
> Note that in this case Node 1 has a sequence cache consisting of values 93 - 112, Node 3 has a cache consisting of values 113 - 132 and Node 2 has a cache consisting of sequence values 133 - 152 -- this is the result of NOORDER. It does not affect the order of the values from the sequence cache, it DOES affect whether each node gets its own cache or works from the global cache.
>
>
>
>
>
> David Fitzjarrell
Thanks David,
Very clarifying!
Got it now :)
Received on Fri Sep 21 2012 - 03:08:20 CDT