This discussion is archived
1 2 3 4 Previous Next 59 Replies Latest reply: Nov 22, 2012 4:27 AM by PeterMarcoen Go to original post RSS
  • 15. Re: Sequence with multiple increments
    KeithJamieson Expert
    Currently Being Moderated
    Hi Peter

    In your case, I would just use one sequence and set the cache size to 1000.

    Let the database take care of the caching. Don't try to get all the numbers on the client side.

    Remember also that sequences are not guaranteed to be gap free.

    Are you aware that you can use a statement such as

    (given here your sequence is called my_seq)
    eg
    create sequence my_seq start with 1 cache 1000;
    insert 
    into my_table
    (seq_id, 
     app_id
    )
    values
    (my_seq.nextval,
     1);
    If you do this , then the database actually fetches 1000 sequence values at a time, and stores them in memory. So after the first value, the next 999 values are obtained incredibly quickly.


    I would not go with incrementing your sequence by 100, or 1000.
    It has been my experience that although Oracle can handle numbers as big as 10 to the power of 38, the front end applications typically can't. I have had personal experience (Due to a sq coding bug on sequences) of having to resequence an entire production database, as the front end could not handle the numbers being returned.

    Edited by: Keith Jamieson on Nov 21, 2012 12:18 PM
    Cache should be set to 1000 not 100
  • 16. Re: Sequence with multiple increments
    PeterMarcoen Newbie
    Currently Being Moderated
    Frank Kulash wrote:
    I saw it, but I still don't understand why incremneting by 1 won't work.
    Well, these applications could be using hundreds of ids per second. I would prefer not to make 100 queries per second. That's why I would like to get x amount of ids with every query (100/1000).
    Why? If you increment by 1000, and you assign 1,000,000 numbers every second, you still won't run out of numbers for millions of years.
    Yeah, obviously, like everyone pointed out, I will not run out of numbers. This would however force me to update existing tables to accept bigger numbers.
    This, however, isn't really that big of a problem and would be a good solution. I just thought there might be a more elegant solution where I didn't need to request more numbers than I actually needed.
  • 17. Re: Sequence with multiple increments
    PeterMarcoen Newbie
    Currently Being Moderated
    This is a good answer.
    Could you teach me how to just get the first (or last) value from that query then ? So that the query eventually only returns 1 value.
  • 18. Re: Sequence with multiple increments
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Use the MAX() function. That simple. (you seem to think Oracle is a lot more complex that what it typically is ;-) )
  • 19. Re: Sequence with multiple increments
    PeterMarcoen Newbie
    Currently Being Moderated
    Billy  Verreynne  wrote:
    Use the MAX() function. That simple. (you seem to think Oracle is a lot more complex that what it typically is ;-) )
    Apparently, it is:

    SQL> select max(seq.nextval) from dual connect by level <= 10;
    select max(seq.nextval) from dual connect by level <= 10
    *
    ERROR at line 1:
    ORA-02287: sequence number not allowed here
  • 20. Re: Sequence with multiple increments
    KeithJamieson Expert
    Currently Being Moderated
    Could you explain how you insert data into the database?

    eg are inserts fired individually, is there an insert in a loop? or do you
    use the
    insert into select from syntax.

    eg insert into table_name(column_list)
    select columnlistvalues
    from table_a,
    table_b etc ...
  • 21. Re: Sequence with multiple increments
    PeterMarcoen Newbie
    Currently Being Moderated
    Keith Jamieson wrote:
    Could you explain how you insert data into the database?

    eg are inserts fired individually, is there an insert in a loop? or do you
    use the
    insert into select from syntax.

    eg insert into table_name(column_list)
    select columnlistvalues
    from table_a,
    table_b etc ...
    We use sqlldr to insert a lot of records at once.
  • 22. Re: Sequence with multiple increments
    AlbertoFaenza Expert
    Currently Being Moderated
    Peter Marcoen wrote:
    This is a good answer.
    Could you teach me how to just get the first (or last) value from that query then ? So that the query eventually only returns 1 value.
    There is no point in getting 1000 numbers at the same time and then use them later on. It's always better to get the sequence number when you need.

    Also getting min/max of 1000 number does not ensure that all numbers are consecutive in case of concurrent operations.

    Please explain how you want to use later on these sequence numbers and why you need to retrieve them all in once.

    Regards.
    Al
  • 23. Re: Sequence with multiple increments
    PeterMarcoen Newbie
    Currently Being Moderated
    Alberto Faenza wrote:
    Peter Marcoen wrote:
    This is a good answer.
    Could you teach me how to just get the first (or last) value from that query then ? So that the query eventually only returns 1 value.
    There is no point in getting 1000 numbers at the same time and then use them later on. It's always better to get the sequence number when you need.

    Also getting min/max of 1000 number does not ensure that all numbers are consecutive in case of concurrent operations.

    Please explain how you want to use later on these sequence numbers and why you need to retrieve them all in once.

    Regards.
    Al
    As I have explained a couple of times before:

    I plan to use these sequence numbers as unique ids, the reason why I want to retrieve them all at once is because my application may need hundreds of unique ids per second. Therefore I would prefer not to have to go to the database hundreds of times per second. I know Oracle is extremely fast in doing this, but the network connection to my database might give me some delay, and even a delay of 0.01 second could result in several seconds wait time when retrieving hundreds of ids.
  • 24. Re: Sequence with multiple increments
    AlbertoFaenza Expert
    Currently Being Moderated
    Hi Peter,

    There is already a thread to solve this: {thread:id=1055483}.

    You can use sequence directly in sqlldr.

    Regards.
    Al
  • 25. Re: Sequence with multiple increments
    Paul Horth Expert
    Currently Being Moderated
    Peter Marcoen wrote:
    Alberto Faenza wrote:
    Peter Marcoen wrote:
    This is a good answer.
    Could you teach me how to just get the first (or last) value from that query then ? So that the query eventually only returns 1 value.
    There is no point in getting 1000 numbers at the same time and then use them later on. It's always better to get the sequence number when you need.

    Also getting min/max of 1000 number does not ensure that all numbers are consecutive in case of concurrent operations.

    Please explain how you want to use later on these sequence numbers and why you need to retrieve them all in once.

    Regards.
    Al
    As I have explained a couple of times before:

    I plan to use these sequence numbers as unique ids, the reason why I want to retrieve them all at once is because my application may need hundreds of unique ids per second. Therefore I would prefer not to have to go to the database hundreds of times per second. I know Oracle is extremely fast in doing this, but the network connection to my database might give me some delay, and even a delay of 0.01 second could result in several seconds wait time when retrieving hundreds of ids.
    But why is your application retrieving the ids before they're needed? What are you going to do with them?

    Normally you get a new sequence value in order to then insert a new row. Is that what you are doing?
  • 26. Re: Sequence with multiple increments
    PeterMarcoen Newbie
    Currently Being Moderated
    Paul Horth wrote:
    But why is your application retrieving the ids before they're needed? What are you going to do with them?

    Normally you get a new sequence value in order to then insert a new row. Is that what you are doing?
    Well, the application will request an id, and use this id to prepare to files for sqlldr.
    Let's use a customer database as an example, because the real thing is a bit too complex to explain.
    File 1 would contain the data of the customers (*id*, name, address, email, ...). This file would be used by sqlldr to insert this data into the customers table.
    File 2 would contain the data of the orders (*customer_id*, item, quantity). This file would be user by sqlldr to insert this data into the orders table.
  • 27. Re: Sequence with multiple increments
    PeterMarcoen Newbie
    Currently Being Moderated
    Alberto Faenza wrote:
    Hi Peter,

    There is already a thread to solve this: {thread:id=1055483}.

    You can use sequence directly in sqlldr.

    Regards.
    Al
    Like I said in my previous answer, I will use this ID not only populate the table that uses this ID as a primary key, but also a secondary table that can have many records per ID
  • 28. Re: Sequence with multiple increments
    KeithJamieson Expert
    Currently Being Moderated
    When you get these 100's of Id's from a sequence,
    what are you going to do with them.

    I assume at some point they get inserted into the database.
    Why is that not going to be a network issue? and getting the sequences are.


    How does the data get into the database?

    eg can you explain in pseudo_code

    eg

    something like :
    select 1000 sequence numbers from db into an array
    
    Update array with application details 
    
    insert array to database
    Perhaps what you need is the returning clause of the insert statement

    eg
    declare
    v_id number;
    begin
        insert 
        into my_table
        (app_id, 
         sequence_id
        )
       (1,
        myseq.nextval
       )
    returning sequence_id 
    into v_id;
  • 29. Re: Sequence with multiple increments
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Peter Marcoen wrote:

    Apparently, it is:

    SQL> select max(seq.nextval) from dual connect by level <= 10;
    select max(seq.nextval) from dual connect by level <= 10
    *
    ERROR at line 1:
    ORA-02287: sequence number not allowed here
    Ah yes - forgot about that... not exactly sure why this behaviour. Easily to work around with in the following fashion:
    SQL> create sequence seq_id
      2          start with 1
      3          increment by 1
      4          nomaxvalue
      5          nocycle;
    
    Sequence created.
    
    SQL> 
    SQL> create or replace function GetNextRange( range number default 100 ) return number is
      2          type TNumbers is table of number;
      3          n       TNumbers;
      4  begin
      5          select
      6                  seq_id.nextval bulk collect into n
      7          from    dual
      8          connect by level <= range;
      9  
     10          return(
     11                  n(n.count)
     12          );
     13  end;
     14  /
    
    Function created.
    
    SQL> 
    SQL> select GetNextRange() from dual;
    
    GETNEXTRANGE()
    --------------
               100
    
    SQL> select GetNextRange() from dual;
    
    GETNEXTRANGE()
    --------------
               200
    
    SQL> select GetNextRange(1000) from dual;
    
    GETNEXTRANGE(1000)
    ------------------
                  1200
    
    SQL> 

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points