How does NOORDER work on Oracle databases?

Question

I have a sequence defined as following:

CREATE SEQUENCE  "myseq"  MINVALUE 1 MAXVALUE 9999999999 INCREMENT BY 1 START WITH 1046 CACHE 20 NOORDER  NOCYCLE ;

Now my customer reported that the system generated the numbers 429521, 42952, 42967, 42968 until 42972, 42987, 4307, 4308.

The sequence is running on a clustered database system with two systems. Could this behaviour be caused by the NOORDER keyword? The Oracle documentation for NOORDER is quite short.

Answer 1

This option just removes the need for the separate instances to coordinate between each other which value they will next release.

Instead, each instance gets a cache of values from which it can draw independently, which improves performance.

So in a multi-instance system you use NOORDER unless you want the sequence numbers to also indicate the order in which the records had a sequence number assigned to them (in general, the order in which they were inserted).

Have you correctly typed those numbers? It seems very improbable that they would come from a two-instance system with that sequence, unless only one of the instances had generated the last 40,000 or so new values, and the other had practically no activity at all.