8 Replies Latest reply: Jan 24, 2013 9:39 AM by Gaff RSS

    gaps in sequence numbers

    user5716448
      Hi,

      Using 11.2.0.3 with code below to try generate sequnec number when no other number found.

      Get seq numbers but gaps greate than 1 in them and not starting at specfied number

      What am I doing wrong?
      execute immediate('drop sequence NON_SAP_BUSINESS_PARTNER_SEQ');
      -- Create sequence 
      execute immediate('create sequence NON_SAP_BUSINESS_PARTNER_SEQ
      minvalue 600225
      maxvalue 699999
      start with 600225
      increment by 1
      nocache');
      
      Thanks
      
      
      CASE WHEN x.ccr_bus_partner_id IS not null
               then x.ccr_bus_partner_id
            else
                nvl(initial_load.RETURN_bp_number(cd_urn,cd_hub_num,cd_legacy_box_num),NON_SAP_BUSINESS_PARTNER_SEQ.nextval)
                
      end case   
        • 1. Re: gaps in sequence numbers
          Paul  Horth
          user5716448 wrote:
          Hi,

          Using 11.2.0.3 with code below to try generate sequnec number when no other number found.

          Get seq numbers but gaps greate than 1 in them and not starting at specfied number

          What am I doing wrong?
          execute immediate('drop sequence NON_SAP_BUSINESS_PARTNER_SEQ');
          -- Create sequence 
          execute immediate('create sequence NON_SAP_BUSINESS_PARTNER_SEQ
          minvalue 600225
          maxvalue 699999
          start with 600225
          increment by 1
          nocache');
          
          Thanks
          
          
          CASE WHEN x.ccr_bus_partner_id IS not null
          then x.ccr_bus_partner_id
          else
          nvl(initial_load.RETURN_bp_number(cd_urn,cd_hub_num,cd_legacy_box_num),NON_SAP_BUSINESS_PARTNER_SEQ.nextval)
          
          end case   
          Sequences are not guaranteed to be contiguous: they can have gaps.
          • 2. Re: gaps in sequence numbers
            BluShadow
            user5716448 wrote:
            Hi,

            Using 11.2.0.3 with code below to try generate sequnec number when no other number found.

            Get seq numbers but gaps greate than 1 in them and not starting at specfied number

            What am I doing wrong?
            execute immediate('drop sequence NON_SAP_BUSINESS_PARTNER_SEQ');
            -- Create sequence 
            execute immediate('create sequence NON_SAP_BUSINESS_PARTNER_SEQ
            minvalue 600225
            maxvalue 699999
            start with 600225
            increment by 1
            nocache');
            
            Thanks
            
            
            CASE WHEN x.ccr_bus_partner_id IS not null
            then x.ccr_bus_partner_id
            else
            nvl(initial_load.RETURN_bp_number(cd_urn,cd_hub_num,cd_legacy_box_num),NON_SAP_BUSINESS_PARTNER_SEQ.nextval)
            
            end case   
            Depending on your statement and code used, the nextval will be determined for each row retrieved in a query (i.e. the other condition of a case may be partially or fully evaluated causing the nextval to be retrieved even if it's not used).

            You shouldn't rely on sequence objects for 'gapless' sequences. That's not what they're designed for and gap free sequences are not guaranteed with them.
            • 3. Re: gaps in sequence numbers
              Gaff
              Sequences have no promise of giving values without gaps to any single caller. The sequence gives the next number to whatever client asks for it. If some other session is using the sequence then the next one you get won't necessarily be the last one you got plus one. Moreover, it doesn't matter. The thing to get from a sequence is a unique number so that you can use it as a primary key or something. The fact that there are "gaps" is meaningless, yet many people seem to get hung up on it. Sad but true.
              • 4. Re: gaps in sequence numbers
                user5716448
                Thanks.

                Will use standalone table fro our purposes.
                • 5. Re: gaps in sequence numbers
                  Gaff
                  Sequences exist because implementing that functionality in tables can have performance issues and side effects. Are you sure gaps really matter for what you are doing?

                  If a sequence isn't working for you because you have multiple sessions (same username) doing something with it simultaneously then you may have similar issues with the table unless you get all of your numbers in one call and the table is locked while you do it, making the other session wait.
                  • 6. Re: gaps in sequence numbers
                    user5716448
                    Yes - need no gaps for this.

                    Jusrt for migrating legacy data to new system which doesn;t have an existing customer no.

                    Trying to use standard insert statement to call function to get the number but get message

                    ORA-14552 cannot commit - was trying to insert into table the unique number whith no gaps and at same time increment tbale holding number by 1.

                    Don't really want to have to process this row-by-row.


                    Possible will use sequnec then later update to rownum + 600000
                    Any other thoughts?
                    • 7. Re: gaps in sequence numbers
                      Gaff
                      You probably have this issue.

                      Calling the function from SQL query
                      • 8. Re: gaps in sequence numbers
                        Gaff
                        I'm still not convinced gaps matter (based on the limited knowledge I have of your situation). If you have a new customer that needs a new number, then perhaps (probably) that customer needs a new number generated that is higher than any existing number customer number. Fine. A sequence should get you that. I can't see how gaps matter but somehow you are planning to replace the generated but gapped numbers with rownum easily if any other table depends on that column (fk?) and in particular depends on that column having gap-less numbers in it.