Re: sequence and OPS

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 Received on Fri Aug 01 2003 - 08:39:17 CDT