Oracle Sequence Number Problme

Discussion in 'Oracle' started by fsloke, Jul 16, 2008.

  1. fsloke

    fsloke New Member

    Joined:
    Jul 16, 2008
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    0
    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
     
  2. xpi0t0s

    xpi0t0s Mentor

    Joined:
    Aug 6, 2004
    Messages:
    3,009
    Likes Received:
    203
    Trophy Points:
    63
    Occupation:
    Senior Support Engineer
    Location:
    England
    have you tried ALTER SEQUENCE...INCREMENT BY?
     
  3. xpi0t0s

    xpi0t0s Mentor

    Joined:
    Aug 6, 2004
    Messages:
    3,009
    Likes Received:
    203
    Trophy Points:
    63
    Occupation:
    Senior Support Engineer
    Location:
    England
    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.
     
  4. fsloke

    fsloke New Member

    Joined:
    Jul 16, 2008
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    0
    It not work brother....

    You will back to my 2nd solution...

    Thank...
     
  5. xpi0t0s

    xpi0t0s Mentor

    Joined:
    Aug 6, 2004
    Messages:
    3,009
    Likes Received:
    203
    Trophy Points:
    63
    Occupation:
    Senior Support Engineer
    Location:
    England
    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.
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice