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

I'm not sure I see your issue.

A sequence is usually called in conjunction with a table insert.
e.g.

It really doesn't matter how may rows are involved, you just ask for the next number each time.

If you want, you can even automate the process, like many other databases do with an auto increment type column. In Oracle, it just takes a trigger.

e.g.

That's really all there is to it. If you're preprocessing data, use a temp table to buffer the stuff and then push the data into its final storage area. To be any more specific, I'd have to see the particulars of what you're doing, PL/SQL and client side if that's involved.

You should not be changing sequences, ever. If you are, then there's a bug in the process.

Hope this helps.