Re: sequence and OPS

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.

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.

Just adding to the debate. -- Mark D Powell -- Received on Sat Aug 02 2003 - 10:15:57 CDT