Go4Expert

Go4Expert (http://www.go4expert.com/)
-   Oracle (http://www.go4expert.com/forums/oracle-forum/)
-   -   Oracle Sequence Number Problme (http://www.go4expert.com/forums/oracle-sequence-problme-t12130/)

fsloke 16Jul2008 08:01

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

xpi0t0s 16Jul2008 14:03

Re: Oracle Sequence Number Problme
 
have you tried ALTER SEQUENCE...INCREMENT BY?

xpi0t0s 16Jul2008 14:06

Re: Oracle Sequence Number Problme
 
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.

fsloke 16Jul2008 14:15

Re: Oracle Sequence Number Problme
 
It not work brother....

You will back to my 2nd solution...

Thank...

xpi0t0s 17Jul2008 13:34

Re: Oracle Sequence Number Problme
 
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.


All times are GMT +5.5. The time now is 22:11.