CODEWITHSUNDEEP
sqloracle-database
Sunmit Girme
Sunmit Girme

Reputation: 569

Oracle Sequence generator syntax

The basic syntax for creating a sequence generator for oracle database table attribute is:

CREATE SEQUENCE customers_seq
START WITH     1000
INCREMENT BY   1
NOCACHE
NOCYCLE;

I would like to know what does the NOORDER clause in the SEQUENCE syntax do? If I include the NOORDER clause, will it give me a sequence keeping the increment value random???

Upvotes: 2

Views: 2557

Answers (1)

Rob van Laarhoven
Rob van Laarhoven

Reputation: 8915

This is important when running in parallel server mode or in a cluster. ORDER guarantees that the sequences numbers are ordered. 

Example of “noorder” sequence in 10g RAC:

Session 1 on node-A: nextval -> 101
Session 2 on node-A: nextval -> 102
Session 1 on node-B: nextval -> 121
Session 1 on node-B: nextval -> 122
Session 1 on node-A: nextval -> 103
Session 1 on node-A: nextval -> 104

NOORDER in combination with CACHING sequences results in this behavior. ORDER effectively makes the CACHE setting unused. With caching set to 10 when a session gets a sequences it takes 10 sequences into it's cache.

Upvotes: 4

Related Questions