This discussion is archived
9 Replies Latest reply: Dec 8, 2012 9:59 PM by jeneesh RSS

Behavior of sequence?

Tomislav Beuk Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thank you very much, explanation was excellent.
    Case solved :-)
  • 9. Re: Behavior of sequence?
    jeneesh Guru
    Currently Being Moderated
    Great peace of information....

Legend

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