CODEWITHSUNDEEP
sqloracle-databasedatabase-sequence
Abinnaya
Abinnaya

Reputation: 223

Sequence id is not properly inserted

I have created a sequence but its not inserting ids in sequence order.

For Ex:

So 5, 6, 7 is wrong, I need 11, 12, 13 to be generated

What's wrong in my below sequence create query?

CREATE SEQUENCE "LEASE_REPAYMENT_SEQ" 
MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1
START WITH 146724 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE GLOBAL ;

Upvotes: 0

Views: 486

Answers (1)

Marmite Bomber
Marmite Bomber

Reputation: 21063

If you check the sequence definition, you'll see that you define it as NOORDER.

The Oracle Documentation says

Specify NOORDER if you do not want to guarantee sequence numbers are generated in order of request. This is the default.

So what you see is an expected bahaviour and I assume your database is a RAC instance (as this effect can be observed on RAC only).

Having said that, there are good reasons to allow this small dis-order of the assigned IDs (which is caused by the caching as each RAC instance gets it own cache size to work with).

The positive side of this apprach is that there is no need to synchronize the sequence between the instances - a task that could produce a big overhead.

Upvotes: 3

Related Questions