Oracle Sequence Number Problme

I created a sequence number call FSLOKE_OWNER.TEST_IDENTIFIER_SQ

CREATE SEQUENCE FSLOKE_OWNER.TEST_IDENTIFIER_SQ
START WITH 1
MAXVALUE 999999999999999999999999999
MINVALUE 1
NOCYCLE
CACHE 20
NOORDER;

This sequence mean that everytime I call

select FSLOKE_OWNER.TEST_IDENTIFIER_SQ.nextval from dual

it will increment by one then return the next value, right?

The situation is:
Now I have 100 data coming in, each one will assign the unique ID to it. So coding in such looping 100 times to call the sequence number one by one to retrieve unique number and assign it to.

After finish transaction, I already call
select FSLOKE_OWNER.TEST_IDENTIFIER_SQ.nextval from dual statement 100 times!!!

For your information, I know well know how many data coming in. Now is 100 but the other times maybe 50, or 200 or even can be 1000. This number I know it.

To increase the performance. My solution is for 100 incoming data, I will "reserve" 100 place for it by adjust the nextvalue increment by 100 by ONE SQL statement.

The senario is like this:
int number = getSequenceNumber(100);
loop(;{
number++
}

I using coding to control it. That why it mean "reserve". It actually adjust the nextVal to 101 in the database.

So how can I adjust the sequence command?

I got try to use

This command cause error [ cannot change the START WITH VALUE ]:
ALTER SEQUENCE FSLOKE_OWNER.TEST_IDENTIFIER_SQ START WITH <PrevSequence+reserveVal>

Then I try this:
===========
int getSequnceNumber(int incrementVal) method
Step 1:
int sequence = select FSLOKE_OWNER.TEST_IDENTIFIER_SQ.nextval from dual;
Step 2:
ALTER SEQUENCE FSLOKE_OWNER.TEST_IDENTIFIER_SQ increment by <incrementVal>
Return sequence;

But this solution solve my problem, but it my application work in multithread. It become worst!!!

Any Sequence statement that can adjust the nextValue?

Thank

 

have you tried ALTER SEQUENCE...INCREMENT BY?

 

Sorry...mis-RTFM. ALTER SEQUENCE ... MINVALUE should change the nextValue; get the next value from the sequence, add 100, ALTER..MINVALUE to the result. That should do the trick.

 

It not work brother....

You will back to my 2nd solution...

Thank...

 

I don't know what "You will back to my 2nd solution" means.

This works; I tried it this time:
create sequence fsloke start with 1 maxvalue 1000000 minvalue 1;
select fsloke.nextval from dual;
=> 1
select fsloke.nextval from dual;
=> 2
alter sequence fsloke increment by 20;
select fsloke.nextval from dual;
=> 22
alter sequence fsloke increment by 1;
select fsloke.nextval from dual;
=> 23

In multithreading you will need to mutex this, otherwise you might get:
thread 1: select nextval => 23 Now it thinks it has values 23~122, except along comes the timeslicer and...
thread 2: select nextval => 24 Now it thinks it has the values 24~123 and now the two threads are going to be inserting duplicate key records for 24~122.
You don't need to serialise calls to nextval; that's already threadsafe, it's where you call nextval then assume you have nextval+1~nextval+99 to yourself; you have to prevent other threads getting hold of nextval+1.

Why are you doing this anyway though? Has profiling shown the nextval call to be a problem, or are you just looking at the high number of calls and assuming it will be? Are you using client/server over a network or connecting to a database on the same machine? Premature optimisation is a root of all kinds of evil so make sure you're performing this optimisation for a good reason, and if you haven't got a good reason just use nextval.