I created a sequence number call FSLOKE_OWNER.TEST_IDENTIFIER_SQ
CREATE SEQUENCE FSLOKE_OWNER.TEST_IDENTIFIER_SQ
START WITH 1
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);
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
int sequence = select FSLOKE_OWNER.TEST_IDENTIFIER_SQ.nextval from dual;
ALTER SEQUENCE FSLOKE_OWNER.TEST_IDENTIFIER_SQ increment by <incrementVal>
But this solution solve my problem, but it my application work in multithread. It become worst!!!
Any Sequence statement that can adjust the nextValue?