Oracle sequence.NEXTVAL strange order from JSP app

571 views Asked by At

I am developing a JSP application in JSP with Oracle 11gr2. I have created a sequence:

CREATE SEQUENCE Movie_SEQ INCREMENT BY 1 MAXVALUE 99999 MINVALUE 1 CACHE 20;

Inserting data from sql developer seems fine: the primary key takes values in order 1,2,3,etc. But when I try to insert row from JSP calling a function:

add_m(Movie_SEQ.NEXTVAL, ....)

which executes a statement like

insert into table_name values(Movie_SEQ.NEXTVAL, ...)

the sequence generates numbers like : 1,2,22,23,30,...not in order. Why?? Could you please explain me what is wrong.

2

There are 2 answers

3
ibrahimbayer On

It is because cache size. Oracle caches count of nextval for fast access of each transaction.

0
Brian On

Oracle:

You cannot expect a sequence to return gap free values. A sequence has one purpose: assign unique numbers to stuff. Nothing else. There will be gaps, gaps are normal, expected, good, ok, fine. They will be there, there is no avoiding them.