Skip to main content

Search

Welcome to Tibero GTS!

Sequence Value Not Assigned Sequentially When Setting CACHE in TAC Environment

Document Type | Troubleshooting

Category | Administration

Applicable Product Version | 6FS07

Document Number | TADTS040

 

Issue

In a TAC environment, when using the NO ORDER + CACHE option for sequence, the sequence values are not assigned in order.

 

 

Cause

In a TAC environment with CACHE + NO ORDER options, after node1 skips sequence numbers equal to the cache size, node2 starts allocating from the subsequent numbers of its allocated cache size.
As a result, the sequence values are effectively not assigned sequentially.
 

1. Sequence creation with NO ORDER option

CREATE SEQUENCE NO_ORDER
INCREMENT BY 1 
START WITH 1 
NOMAXVALUE
MINVALUE 1 
NOCYCLE 
CACHE 5 
NOORDER ;

 

2. Query results

When selecting select NO_ORDER.nextval from dual; more times than the CACHE value allocated to NODE2 from NODE1, it can be confirmed that the sequence values are not assigned sequentially. 

SQL> select NO_ORDER.nextval from dual;


NEXTVAL

1


1 row selected.


SQL> select NO_ORDER.nextval from dual;


NEXTVAL

2


1 row selected.


SQL> select NO_ORDER.nextval from dual;


NEXTVAL

5


1 row selected.


SQL> select NO_ORDER.nextval from dual;


NEXTVAL

11


1 row selected.

 

 

Solutions

When using the ORDER option, the sequence numbers do not skip to the cache size allocated to other nodes and are assigned sequentially.
Note
Using the order option may cause performance overhead. It is recommended to use CACHE and ORDER/NO ORDER options according to the site environment.

 

 
 
 

Related to