9 Replies Latest reply: Dec 8, 2012 11:59 PM by jeneesh RSS

    Behavior of sequence?

    Tomislav Beuk
      Hi,

      I cannot understand the behavior of a sequence.

      This is the first part of a script:

      drop table zaposlenik;

      create table zaposlenik
      +(id_zap number(5), ime varchar2(20));+

      drop sequence zaposlenik_seq;

      CREATE SEQUENCE zaposlenik_seq
      MINVALUE 0
      START WITH 0
      INCREMENT BY 1
      CACHE 20;


      insert into zaposlenik
      values (zaposlenik_seq.NEXTVAL, 'tom');


      insert into zaposlenik
      values (zaposlenik_seq.NEXTVAL, 'ivancica');

      commit


      When I run the script, first value of id_zap is 1. But when i run the second part
      of the script and use truncate, first value of id_zap is 0. Second part of the script:


      truncate table zaposlenik;

      drop sequence zaposlenik_seq;

      CREATE SEQUENCE zaposlenik_seq
      MINVALUE 0
      START WITH 0
      INCREMENT BY 1
      CACHE 20;


      insert into zaposlenik
      values (zaposlenik_seq.NEXTVAL, 'tom');

      insert into zaposlenik
      values (zaposlenik_seq.NEXTVAL, 'ivancica');

      commit

      Thanks for your responses in advance.
        • 1. Re: Behavior of sequence?
          Solomon Yakobson
          Well, in second part you drop sequence. Then you create a <b>new+</b> sequence with same name, so obviously values start with zero again. If you ALTER SEQUENCE, NEXTVAL will continue from where it left of:
          SQL> create table zaposlenik
            2  (id_zap number(5), ime varchar2(20));
          Table created.
          
          SQL> drop sequence zaposlenik_seq;
          Dropping SEQUENCE ZAPOSLENIK_SEQ
          drop sequence zaposlenik_seq
                        *
          ERROR at line 1:
          ORA-02289: sequence does not exist
          
          
          SQL> CREATE SEQUENCE zaposlenik_seq
            2  MINVALUE 0
            3  START WITH 0
            4  INCREMENT BY 1
            5  CACHE 20;
          Sequence created.
          
          SQL> insert into zaposlenik
            2  values (zaposlenik_seq.NEXTVAL, 'tom');
          
          1 row created.
          
          SQL> insert into zaposlenik
            2  values (zaposlenik_seq.NEXTVAL, 'ivancica');
          
          1 row created.
          
          SQL> alter sequence zaposlenik_seq maxvalue 1000;
          Sequence altered.
          
          SQL> insert into zaposlenik
            2  values (zaposlenik_seq.NEXTVAL, 'sam');
          
          1 row created.
          
          SQL> select  *
            2    from  zaposlenik
            3  /
          
              ID_ZAP IME
          ---------- --------------------
                   0 tom
                   1 ivancica
                   2 sam
          
          SQL> 
          SY.
          • 2. Re: Behavior of sequence?
            Frank Kulash
            Hi,

            Welcome to the forum!
            968170 wrote:
            Hi,

            I cannot understand the behavior of a sequence.

            This is the first part of a script:

            drop table zaposlenik;

            create table zaposlenik
            +(id_zap number(5), ime varchar2(20));+

            drop sequence zaposlenik_seq;

            CREATE SEQUENCE zaposlenik_seq
            MINVALUE 0
            START WITH 0
            INCREMENT BY 1
            CACHE 20;


            insert into zaposlenik
            values (zaposlenik_seq.NEXTVAL, 'tom');


            insert into zaposlenik
            values (zaposlenik_seq.NEXTVAL, 'ivancica');

            commit


            When I run the script, first value of id_zap is 1.
            When I run your code, the first value returned by the sequence is 0. Post your complete sciript, including the code that you're using to determine that the first value is 1.

            I tested it in Oracle 10.2.0.1.0. What version of Oracle are you using?
            • 3. Re: Behavior of sequence?
              rp0428
              >
              Well, in second part you drop sequence. Then you create a new sequence with same name, so obviously values start with zero again.
              >
              Yes - but how does that answer OPs question? OP is saying that the FIRST value is 1 for the first part of the script and that is AFTER dropping and recreating the sequence also.
              >
              When I run the script, first value of id_zap is 1. But when i run the second part
              of the script and use truncate, first value of id_zap is 0

              • 4. Re: Behavior of sequence?
                Tomislav Beuk
                I am using version 11.2.0.

                -- first part
                drop table zaposlenik;

                create table zaposlenik
                +(id_zap number(5), ime varchar2(20));+

                drop sequence zaposlenik_seq;

                CREATE SEQUENCE zaposlenik_seq
                MINVALUE 0
                START WITH 0
                INCREMENT BY 1
                CACHE 20;


                insert into zaposlenik
                values (zaposlenik_seq.NEXTVAL, 'tom');


                insert into zaposlenik
                values (zaposlenik_seq.NEXTVAL, 'ivancica');

                commit

                -- second part
                truncate table zaposlenik;

                drop sequence zaposlenik_seq;

                CREATE SEQUENCE zaposlenik_seq
                MINVALUE 0
                START WITH 0
                INCREMENT BY 1
                CACHE 20;


                insert into zaposlenik
                values (zaposlenik_seq.NEXTVAL, 'tom');

                insert into zaposlenik
                values (zaposlenik_seq.NEXTVAL, 'ivancica');

                commit
                • 5. Re: Behavior of sequence?
                  Solomon Yakobson
                  >
                  Yes - but how does that answer OPs question?
                  >
                  You are right. I misunderstood the question. But now, I do have the answer. I bet OP is on 11g where by deafult table is created with deferred segment creation. As a result, and it is a known behavior, first insert into the table realizes there is no segment only when it tries to insert first row. At that time oracle creates segment and repeats insert. Unfortunately, first sequence value generated by failed first row insert is lost. I believe oracle doesn't check segment existence before insert for performance reasons - such check would be waste of time everywhere but very first insert. If OP drops and recreates table in second part of the script instead of truncating it, value zero will be lost again. If OP creates table without deferring segment creation first insert will get value 0.

                  SY.
                  • 6. Re: Behavior of sequence?
                    Tomislav Beuk
                    Yes, I am using 11g. I believe you are right, although I didn't understand everything you said :-)
                    So basically, it has something to do with version of oracle?
                    • 7. Re: Behavior of sequence?
                      Solomon Yakobson
                      968170 wrote:
                      So basically, it has something to do with version of oracle?
                      In 11.2 oracle introduced deferred segment creation. Now, by default, when you create a table no space is allocated to table and no segment is created:
                      SQL> select  *
                        2    from  v$version
                        3  /
                      
                      BANNER
                      --------------------------------------------------------------------------------
                      Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
                      PL/SQL Release 11.2.0.1.0 - Production
                      CORE    11.2.0.1.0      Production
                      TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
                      NLSRTL Version 11.2.0.1.0 - Production
                      
                      SQL> create table zaposlenik
                        2  (id_zap number(5), ime varchar2(20));
                      
                      Table created.
                      
                      SQL> 
                      SQL> select  count(*)
                        2    from  user_segments
                        3    where segment_name = 'ZAPOSLENIK'
                        4  /
                      
                        COUNT(*)
                      ----------
                               0
                      
                      SQL> 
                      Then you create a sequence and issue:
                      SQL> insert into zaposlenik
                        2  values (zaposlenik_seq.NEXTVAL, 'tom');
                      
                      1 row created.
                      
                      SQL> select  *
                        2    from  zaposlenik
                        3  /
                      
                          ID_ZAP IME
                      ---------- --------------------
                               1 tom
                      
                      SQL>  
                      What happens? Why 1 and not 0? Oracle gets zaposlenik_seq.NEXTVAL, which is 0. Now Oracle is ready to insert. But there is no segment, so insert raises an exception. This exception is internally captured and handled by Oracle. In exception handler it creates table zaposlenik segment and repeats insert. So now Oracle again gets zaposlenik_seq.NEXTVAL, which is now 1 and does the insert. This time insert succeeds, but first generated sequence value is lost.
                      To prove it is a result of deferred segment creation I will explicitly tell Oracle to create segment at table create time:
                      SQL> drop table zaposlenik purge
                        2  /
                      
                      Table dropped.
                      
                      SQL> drop sequence zaposlenik_seq
                        2  /
                      
                      Sequence dropped.
                      
                      SQL> CREATE SEQUENCE zaposlenik_seq
                        2  MINVALUE 0
                        3  START WITH 0
                        4  INCREMENT BY 1
                        5  CACHE 20;
                      
                      Sequence created.
                      
                      SQL> create table zaposlenik
                        2  (id_zap number(5), ime varchar2(20))
                        3  segment creation immediate
                        4  /
                      
                      Table created.
                      
                      SQL> select  count(*)
                        2    from  user_segments
                        3    where segment_name = 'ZAPOSLENIK'
                        4  /
                      
                        COUNT(*)
                      ----------
                               1
                      
                      SQL> insert into zaposlenik
                        2  values (zaposlenik_seq.NEXTVAL, 'tom');
                      
                      1 row created.
                      
                      SQL> select  *
                        2    from  zaposlenik
                        3  /
                      
                          ID_ZAP IME
                      ---------- --------------------
                               0 tom
                      
                      SQL>  
                      I hope it is clear now.

                      SY.
                      • 8. Re: Behavior of sequence?
                        Tomislav Beuk
                        Thank you very much, explanation was excellent.
                        Case solved :-)
                        • 9. Re: Behavior of sequence?
                          jeneesh
                          Great peace of information....