8 Replies Latest reply: Jul 23, 2012 12:33 PM by Brian Bontrager RSS

    Sequence generation issue

    user512219
      Hi , I am working in Healthcare industry . we have a claim and payment matching process . Every day 1.5 million payments get matched with 70 million claims and the matched payments are stores in some output table . After matching, an oracle seqence number is generated for each matched record and that is the primary key for the output table.

      Match query will be like this,.

      Select seqeunce1.nextval,a.column1,a.column2,b.coulmn1,b.column2 from claim_table a, payment_table b
      where a.column3=b.column3

      We are facing some seqence problem here. some sequence are missed during the first day and the missed sequnces are getting generated in the second day.

      Sequence Query:
      CREATE SEQUENCE ERA_CLAIM_MATCH_S
      START WITH 1
      MAXVALUE 999999999999999999999999999
      MINVALUE 1
      NOCYCLE
      CACHE 500
      NOORDER;

      Example to demonstrated the problem:


      Day 1(15th July) : sequence generated from 1 to 1000000 (some sequence are missed in between)
      Day2 (16th July) : sequence generated from 2500 to 2000000 (some missed sequence in day 1 are generated here)


      Our database in in RAC system.
        • 1. Re: Sequence generation issue
          sb92075
          user512219 wrote:
          Hi , I am working in Healthcare industry . we have a claim and payment matching process . Every day 1.5 million payments get matched with 70 million claims and the matched payments are stores in some output table . After matching, an oracle seqence number is generated for each matched record and that is the primary key for the output table.

          Match query will be like this,.

          Select seqeunce1.nextval,a.column1,a.column2,b.coulmn1,b.column2 from claim_table a, payment_table b
          where a.column3=b.column3

          We are facing some seqence problem here. some sequence are missed during the first day and the missed sequnces are getting generated in the second day.

          Sequence Query:
          CREATE SEQUENCE ERA_CLAIM_MATCH_S
          START WITH 1
          MAXVALUE 999999999999999999999999999
          MINVALUE 1
          NOCYCLE
          CACHE 500
          NOORDER;

          Example to demonstrated the problem:


          Day 1(15th July) : sequence generated from 1 to 1000000 (some sequence are missed in between)
          Day2 (16th July) : sequence generated from 2500 to 2000000 (some missed sequence in day 1 are generated here)


          Our database in in RAC system.
          You have to choose between the alternatives below:
          1) fix the application code
          2) file a Bug Report with Oracle.
          • 2. Re: Sequence generation issue
            user512219
            Do we have any solution to fix the application code .
            • 3. Re: Sequence generation issue
              Billy~Verreynne
              user512219 wrote:

              We are facing some seqence problem here. some sequence are missed during the first day and the missed sequnces are getting generated in the second day.
              Correct. Expected behaviour. As documented.

              Which it seems like you have chosen not read?

              A sequence DOES NOT GUARANTEE gap free numbers.

              On a RAC, each instance will cache a number of available sequence number and hand these out. Thus one node can have lower/earlier sequence numbers than another node - and use these sequences while the other node is using higher/later sequence numbers.

              The purpose of a sequence is to serve as a surrogate key. The ONLY goal of a surrogate key is UNIQUE identification via a UNIQUE value. And that unique value is what a sequence deliver.

              If you want a gap-free sequence number, and have that implemented and working across RAC instances... well, then you have a major lack in understanding server concepts such as serialisation of resources. And how this literally kills performance. Like making a 32 CPU server with 128GB of RAM looking as as slows as yesterday's smartphone on a bad 3G link.
              • 4. Re: Sequence generation issue
                Billy~Verreynne
                user512219 wrote:
                Do we have any solution to fix the application code .
                The data model is broken by adding information to a surrogate key that has no place as part of a surrogate key. A surrogate key/sequence determines uniqueness. Not the order in which the row was created.

                If you want such an order, you need to look at WHY. What the actual business requirement is.

                Arbitrarily trying to impose some kind of sequence order is not sensible. Even a timer to the 100th second does not really indicate which row was first and which row was second - as WHEN that row was created is grey definition area. The RDBMS environment is a multi-user and multi-process environment. Stuff happens at the same time. All the time. Transaction 1 can start at t1 and commit at t4. Transaction 2 can start at t2 and commit at t3. Which one was first?
                • 5. Re: Sequence generation issue
                  Hemant K Chitale
                  I think that the question is : If the highest value generated on Day 1 was 1000000,  how could a value of 2500 be generated on Day 2 ?
                  Shouldn't happen if the Sequence is a NOCYCLE.

                  Hemant K Chitale
                  • 6. Re: Sequence generation issue
                    Billy~Verreynne
                    Hemant K Chitale wrote:
                    I think that the question is : If the highest value generated on Day 1 was 1000000,  how could a value of 2500 be generated on Day 2 ?
                    Shouldn't happen if the Sequence is a NOCYCLE.
                    Hemant, but that is possible if Day 1's processing used the sequence on RAC instance 1 - whereas on Day 2, RAC instance 2 (with unused cached sequences from yesterday) is used.

                    E.g.
                    // on node 1
                    SQL> create sequence testseq start with 1 increment by 1 nomaxvalue nocycle;
                    
                    Sequence created.
                    
                    SQL> select testseq.nextval, sys_context( 'user', 'instance_name' ) as instance from dual;
                    
                       NEXTVAL INSTANCE
                    ---------- ----------
                             1 dev_1
                    
                    
                    // on node 4
                    SQL> select testseq.nextval, sys_context( 'user', 'instance_name' ) as instance from dual;
                    
                       NEXTVAL INSTANCE
                    ---------- ----------
                            21 dev_4
                    
                    
                    // on node 1, use the sequence a couple of times
                    SQL> declare i integer; begin loop i := testseq.nextval(); exit when i > 100000; end loop; end;
                      2  /
                    
                    PL/SQL procedure successfully completed.
                    
                    SQL> select testseq.nextval, sys_context( 'user', 'instance_name' ) as instance from dual;
                    
                       NEXTVAL INSTANCE
                    ---------- ----------
                        100002 dev_1
                    
                    SQL> 
                    
                    
                    // on node 4, check the sequence
                    SQL> select testseq.nextval, sys_context( 'user', 'instance_name' ) as instance from dual;
                    
                       NEXTVAL INSTANCE
                    ---------- ----------
                            22 dev_4
                    • 7. Re: Sequence generation issue
                      Hemant K Chitale
                      I assume or assumed that both instances are/were used on Day1.

                      Hemant K Chitale
                      • 8. Re: Sequence generation issue
                        Brian Bontrager
                        The Sequence is defined with NOORDER. This allows the situation Billy Verreynne demonstrated.

                        http://docs.oracle.com/cd/B13789_01/server.101/b10759/statements_6014.htm
                        >
                        Specify ORDER to guarantee that sequence numbers are generated in order of request. This clause is useful if you are using the sequence numbers as timestamps. Guaranteeing order is usually not important for sequences used to generate primary keys.

                        ORDER is necessary only to guarantee ordered generation if you are using Oracle Database with Real Application Clusters. If you are using exclusive mode, sequence numbers are always generated in order.
                        >

                        Note: This is not gap prevention, what everybody else said on that still applies.