Re: sequence and OPS

On 2 Aug 2003 08:15:57 -0700, Mark D Powell <Mark.Powell_at_eds.com> wrote:

> quarkman <quarkman_at_myrealbox.com> wrote in message
> news:<oprs8avrluzkogxn_at_haydn>...
>> On 31 Jul 2003 19:46:50 -0700, Daud <daud11_at_hotmail.com> wrote:
>>
>> > What's the problem with using Oracle sequences in an OPS/RAC?
>> >
>> > thanks
>> > Daud
>>
>>
>> The problem is that the sequence can serve numbers to different users
>> connected to different instances, and if you've used the CACHE clause on
>> them, that means the numbers may not be allocated 'chronologically'. In
>> other words, you may see rows in a table with an ID column fed by a
>> sequence, with IDs 1,21,22,2,23,3,4,24,5... etc etc. Yet, each of these
>> rows was inserted one after the other.
>>
>> In short, the sequence guarantees uniqueness, but not a chronological
>> ordering.
>>
>> You can of course specify an ORDERED clause when creating a sequence.
>>
>> Brilliant. That would result in the previous rows appearing as
>> 1,2,3,4,5,6,7 etc etc.
>>
>> Only problem is: you can't say CACHE and ORDERED simultaneously. So if
>> you have it ordered, you can't have it cached, and that means a
>> potentially awful point of contention, and woeful performance.
>>
>> (Incidentally, you can indeed specify CACHE and ORDERED at the same
>> time, but then the ORDERED clause is silently ignored, without a word of
>> warning) .
>>
>> ~QM
>
> QM, order is purely a logical concept and using the ORDERED parameter
> on a sequence does not guarentee the commit order of the rows by the
> users or the physically stored order of the rows. I realize you did
> not state that it did, but listing the rows as 1, 2, 3,.... makes it
> appear that is what you get when in reality, when and if the user
> commits, what blocks are in the buffer, and the space avallable for
> inserts in them affect the physical distribution of the rows so the
> rows may not appear in sequence order when selected. Ordering of data
> is only guarenteed by use of an order by clause.
>

Fair enough. The perils of trying to explain things in language anyone can understand.

The point is that that instances won't co-ordinate their issuing of sequences if they're cached. Or they can co-ordinate, but then they're not cached. Make your choice. RAC can't cache and co-ordinate.

> Also when OPS/RAC is involved the use of a timestamp does not
> guarentee true chronological order either. The clocks on the parallel
> nodes may differ slightly so in fact the rows with a lesser timestamp
> could have been inserted later.

I didn't meant to imply true *chronological* co-ordinatiion, merely issuing of sequences of numbers in a co-ordinated cross-node fashion.

~QM

>
> Just adding to the debate. -- Mark D Powell --

" Received on Sat Aug 02 2003 - 12:06:33 CDT