Results 1 to 6 of 6

Thread: sequence

  1. #1
    Join Date
    Jun 2003
    Posts
    45

    Unanswered: sequence

    Hi all,

    A gap is forming when using sequence? what is the reson?

    Pls explain
    Regads
    sudheer

  2. #2
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    How do you know there are gaps? If you are doing something like this:

    SELECT myseq.NEXTVAL FROM dual;

    to find out what "the next value" will be, you are actually incrementing the sequence.

    JoeB

  3. #3
    Join Date
    Feb 2004
    Posts
    20
    try seq.currval

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: sequence

    Sequences deliberately allow gaps, because in a multi-user database attempting to avoid gaps in numbering is:
    a) a performance killer,
    b) effectively impossible

    Imagine a simple system with just 2 users, with a "guaranteed gap-free" sequence (if such existed) that currently stands at value 123.

    User1> get and use next sequence value - 124
    User2> get and use next sequence value - 125
    User1> rollback
    User2> commit

    Now there is a gap, because User1 obtained value 124 but then "wasted" it. OK, so maybe we should "lock" the sequence to prevent that happening?

    User1> get and use next sequence value - 124
    User2> ask for next sequence value - WAIT....
    User1> rollback - unused number 124 "returned to sequence"
    User2> ...get and use next sequence value - 124

    Now we have no gap, but we have "serialized" the sequence - while one user is using it, everyone else waits and waits...

    Given that for these reasons sequences are definitely not gap-free anyway, further advantage is then taken of this to improve performance by caching sequence values. Typically, when user1 gets a sequence number for the first time, he is actually allocated a cache of say 20 numbers e.g. 101-120 to use or not. User 2 will then get 121-140 and so on. Of course, User1 may only use 1 of these numbers, leaving a gap of 19 values.

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    Tony is correct.

    Expecting gapless sequences is unrealistic.

    Just consider that oracle caches sequences
    (so they are ready to be used when you need them).
    You will lose the cached sequences as a gap every time the db is shutdown.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Feb 2004
    Posts
    2
    Or you could create your sequence with the 'NOCACHE' option, such as

    CREATE SEQUENCE dbuser.seq_name
    START WITH 1
    INCREMENT BY 1
    NOMINVALUE
    NOMAXVALUE
    NOCYCLE
    NOCACHE
    /

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •