Wednesday, February 27, 2008

Sequence on RAC with ORDER option

Refer: Specify ORDER to guarantee that sequence numbers are generated in order of request. This clause is useful if you are using the sequence numbers as timestamps. Guaranteeing order is usually not important for sequences used to generate primary keys.

USER@DB4> create sequence test1 start with 1 INCREMENT BY 1 cache 100;
or
USER@DB4> create sequence test1 start with 1 INCREMENT BY 1 cache 100 noorder;
Sequence created.

USER@DB4> select test1.nextval from dual;
NEXTVAL
----------
1

SQL> /
NEXTVAL
----------
2

After that Go to another Node:

USER@DB1> select test1.nextval from dual;

NEXTVAL
----------
101

----------------------------------------------------

USER@DB4> create sequence test2 start with 1 INCREMENT BY 1 cache 100 order;
Sequence created.

USER@DB4> select test2.nextval from dual;
NEXTVAL
----------
1

USER@DB4> /

NEXTVAL
----------
2

After that Go to another Node:

USER@DB1> select test2.nextval from dual;
NEXTVAL
----------
3

USER@DB1> /
NEXTVAL
----------
4

Wow...........

1 comment:

Anonymous said...

It will really work on real time .

cache order will lead to DB performance issues.