1 2 3 4 Previous Next 59 Replies Latest reply: Nov 22, 2012 6:27 AM by Peter Marcoen RSS

    Sequence with multiple increments

    Peter Marcoen
      In our database we have a sequence that is used by 2 different applications.

      Application A requests *1000* ids, that it will use.
      Application B requests *100* ids.

      One possible solution could be the following:

      The sequence is created with increment *1*.
      The application requests the nextval.
      The application drops the sequence.
      The application recreates the sequence with the value it got + 100/1000.

      The problem here is that these applications consist of multiple threads. When thread 1 drops the sequence and thread 2 requests the nextval, the application will fail.

      Another solution could be the following:

      The sequence is created with increment *1*.
      The application requests the nextval.
      The application increments the sequence with 100/1000.

      Here I am a bit worried about thread 2 requesting the nextval after thread 1 requested the nextval but before thread 1 incremented the sequence.
      Maybe if executed as 1 transaction, this problem will not occur?

      I would really like to hear your thoughts on this.
        • 1. Re: Sequence with multiple increments
          908002
          Why to crete only one sequence..why not two sequences based on the what ever condistions/ attributes which is not need of multiple drop and create staatements from application code.
          • 2. Re: Sequence with multiple increments
            Billy~Verreynne
            What is the problem?

            The sequence generator object in Oracle will supply unique numbers (as "thread-safe"). Nothing more. Nothing less.

            If you think that sequences indicate some physical or time order without gaps, you are mistaken. Both technically and data modelling/relational design wise.
            • 3. Re: Sequence with multiple increments
              Keith Jamieson
              It appears you want to know which sequence id was created by which application.

              Solution 1
              Use the one sequence for both applications and add an additional column indicating which application
              inserted the record into the table.

              Solution 2

              Use a separate sequence for each application.

              For application a, start with 1 increment by 2 (only odd numbers)

              For application b, start with 2 increment by 2 (only even numbers)

              Solution 3

              Use the same sequence.
              Create a separate table holding the sequence_id and the application_id

              For what it's worth I woudl use solution 1.
              • 4. Re: Sequence with multiple increments
                Frank Kulash
                Hi,
                Peter Marcoen wrote:
                In our database we have a sequence that is used by 2 different applications.
                What does the sequence number represent? Why can't you use 2 different sequences?
                Application A requests *1000* ids, that it will use.
                Application B requests *100* ids.
                Are you saying that a total of 1100 numbers will be assigned?
                One possible solution could be the following:

                The sequence is created with increment *1*.
                The application requests the nextval.
                The application drops the sequence.
                The application recreates the sequence with the value it got + 100/1000.

                The problem here is that these applications consist of multiple threads. When thread 1 drops the sequence and thread 2 requests the nextval, the application will fail.
                There are lots of problems with dropping and re-creating the sequence. Whatever the problem is, that's probably not the solution.
                Another solution could be the following:

                The sequence is created with increment *1*.
                The application requests the nextval.
                The application increments the sequence with 100/1000.
                Are you saying that sometimes you want the sequence to increment by 100, and at other times you want it to increment by 1000? Why? Why not use the same increment all the time? Why not increment by 1?
                Here I am a bit worried about thread 2 requesting the nextval after thread 1 requested the nextval but before thread 1 incremented the sequence.
                Maybe if executed as 1 transaction, this problem will not occur?

                I would really like to hear your thoughts on this.
                Explain what you want to do, and someone will help you find a good way to do it.
                • 5. Re: Sequence with multiple increments
                  Peter Marcoen
                  I don't really care which sequence id is created by which application.

                  I cannot create 2 seperate sequences because both applications need the same unique id.

                  Let's say these applications insert new clients and use this sequence as a client id.

                  I cannot have application 1 create client ids 1 and 2 for clients a and b, while application 2 creates client ids 1 and 2 for clients c and d.
                  • 6. Re: Sequence with multiple increments
                    Frank Kulash
                    Hi,
                    Peter Marcoen wrote:
                    I don't really care which sequence id is created by which application.

                    I cannot create 2 seperate sequences because both applications need the same unique id.

                    Let's say these applications insert new clients and use this sequence as a client id.

                    I cannot have application 1 create client ids 1 and 2 for clients a and b, while application 2 creates client ids 1 and 2 for clients c and d.
                    I see. If that's all you need to do, then create 1 sequence, use it in both applications, and never change the increment. I would use INCREMENT BY 1, but if you prefer to use 100 or 1000, go ahead; you probably won't use up all the numbers.
                    • 7. Re: Sequence with multiple increments
                      Peter Marcoen
                      Frank Kulash wrote:
                      What does the sequence number represent? Why can't you use 2 different sequences?
                      See answer above.
                      Are you saying that a total of 1100 numbers will be assigned?
                      Are you saying that sometimes you want the sequence to increment by 100, and at other times you want it to increment by 1000? Why? Why not use the same increment all the time? Why not increment by 1?
                      Application A will request 1000 ids x amount of times, application B will request 100 ids x amount of times.
                      Why 1000 and 100 ?

                      Well, application A works at a very high speed, going to the database every time for a new id would slow it down.
                      Application B works at a high speed too, but it really only needs 100 ids every time because it will not use anymore.

                      I could use 1000 ids for both applications but than application B will not use 900 of them, which seems like a huge waste.

                      I hope this clarifies.
                      • 8. Re: Sequence with multiple increments
                        Paul  Horth
                        Peter Marcoen wrote:
                        I don't really care which sequence id is created by which application.

                        I cannot create 2 seperate sequences because both applications need the same unique id.

                        Let's say these applications insert new clients and use this sequence as a client id.

                        I cannot have application 1 create client ids 1 and 2 for clients a and b, while application 2 creates client ids 1 and 2 for clients c and d.
                        Still don't understand. Why not use one sequence? Then you will get unique ids.

                        If you want to know which application created the row: use a separate column.
                        • 9. Re: Sequence with multiple increments
                          Dave Rabone
                          Keith has given some good suggestions, though we don't know exactly what problem you are trying to solve yet.

                          Whatever solution you decide to adopt, do not pursue your original idea ... Dropping and recreating sequences within the application is just plain wrong.
                          • 10. Re: Sequence with multiple increments
                            Paul  Horth
                            Peter Marcoen wrote:
                            Frank Kulash wrote:
                            What does the sequence number represent? Why can't you use 2 different sequences?
                            See answer above.
                            Are you saying that a total of 1100 numbers will be assigned?
                            Are you saying that sometimes you want the sequence to increment by 100, and at other times you want it to increment by 1000? Why? Why not use the same increment all the time? Why not increment by 1?
                            Application A will request 1000 ids x amount of times, application B will request 100 ids x amount of times.
                            Why 1000 and 100 ?

                            Well, application A works at a very high speed, going to the database every time for a new id would slow it down.
                            Don't worry, if you give the sequence a reasonable cache value, Oracle is quite capable of high-speed transactional processing :-)

                            Just use one sequence. Don't worry about wasting sequence ids: the orphaned ones are sent to a recycling centre and re-used in other databases
                            and none are harmed in the process :-)
                            • 11. Re: Sequence with multiple increments
                              Peter Gjelstrup
                              Peter Marcoen wrote:
                              Well, application A works at a very high speed, going to the database every time for a new id would slow it down.
                              Application B works at a high speed too, but it really only needs 100 ids every time because it will not use anymore.
                              Take all numbers you want in one round trip then:
                              select s.nextval from dual connect by level <= 10;
                              
                              
                                 NEXTVAL
                              ----------
                                      24
                                      25
                                      26
                                      27
                                      28
                                      29
                                      30
                                      31
                                      32
                                      33
                              
                              10 rows selected.
                              Regards
                              Peter
                              • 12. Re: Sequence with multiple increments
                                Frank Kulash
                                Hi,
                                Peter Marcoen wrote:
                                Frank Kulash wrote:
                                What does the sequence number represent? Why can't you use 2 different sequences?
                                See answer above.
                                I saw it, but I still don't understand why incremneting by 1 won't work.
                                Are you saying that a total of 1100 numbers will be assigned?
                                Are you saying that sometimes you want the sequence to increment by 100, and at other times you want it to increment by 1000? Why? Why not use the same increment all the time? Why not increment by 1?
                                Application A will request 1000 ids x amount of times, application B will request 100 ids x amount of times.
                                Why 1000 and 100 ?

                                Well, application A works at a very high speed, going to the database every time for a new id would slow it down.
                                Application B works at a high speed too, but it really only needs 100 ids every time because it will not use anymore.
                                Are you sayng that each application is starting with the sequence number, and then incrementing it by 1 to get unique ids? Is that really faster than using a sequence to get unique ids, especially with a large cache size? If it is faster, then why use sequences at all? Have one application generate positive numbers, and the other one generate negative numbers (or have one generate even numbers, and the other one odd numbers) and you'll never have to bother with those slow Oracle sequences.
                                If you're concerned about wasting numbers, why waste any? Always increment by 1.
                                I could use 1000 ids for both applications but than application B will not use 900 of them, which seems like a huge waste.
                                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.
                                I hope this clarifies.
                                Sorry, no. I don't see why a single sequence, incrementing by 1, won't work.
                                • 13. Re: Sequence with multiple increments
                                  Dave Rabone
                                  Don't worry about "wasting" numbers ... Make the id column number data type, and you have 10**38 values to play with. Throw away a billion per second and you'll run out in 10**30 seconds ... (10**24 years)
                                  • 14. Re: Sequence with multiple increments
                                    jeneesh
                                    Peter Marcoen wrote:
                                    I could use 1000 ids for both applications but than application B will not use 900 of them, which seems like a huge waste.
                                    Waste?


                                    What way? Are you spending any money on it..?

                                    And do you know - the maximum allowed value for a incrementing sequence by DEFAULT is power(10,27).

                                    If you are generating 100,000 values per second (A lot will be WASTE) it will take around 317097919837646 years to expire (this was discuused in some other thread few days back..)
                                    select round(power(10,27)/(100000*60*60*24*365)) yrs
                                    from dual;
                                    
                                    
                                    YRS
                                    ----------
                                    317097919837646
                                    1 2 3 4 Previous Next