Re: Why sequence jumps?

Hi, the another way to avoid seq gap is to create before-insert database trigger (:new.col_id=seq.nextval), this also improve performance. :-)

HTH
celia

In article <377742c1.5321970_at_newshost.uk.oracle.com>,   mplant_at_uk.oracle.com (Mark Plant) wrote:
> Wassim
>
> No problem.
>
> You have not used the CACHE N or the NOCACHE parameters on the CREATE
> SEQUENCE request. This means that Oracle will allocate sequence
> numbers 20 at a time to each user.
>
> If a user uses all of his numbers up, he gets 20 more.
>
> If a user does not use up all 20, the ones not used are discarded.
> That is why you are seeing gaps in the primary key.
>
> Hope this explains the behaviour you are observing. Even if you
> specify CACHE 1 or NOCACHE, you will still get gaps if a user starts a
> transaction which uses a sequence number, but then causes the
> transaction to roll back.
>
> For more detail see the Oracle SQL LANGUAGE AND REFERENCE MANUAL.
>
> Mark
>
> On Sun, 27 Jun 1999 16:45:30 GMT, Wassim <net2000_at_francemel.com>
> wrote:
>
> >Hi,
> >I'm using Sequences (as many of you told me to do, for generating a
> >primary keys in some tables) Strange phenomenen i've noticed is that
in
> >some cases counter jumps from 21 to 40 or 11 to 34 ? this is source
of
> >my sequences :
> >
> >create sequence mvt_seq
> >start with 0
> >increment by 1
> >nomaxvalue
> >minvalue 0
> >nocycle
> >order (cos order is important for me!)
> >
> >Is there any problem with this?
> >Thanks for help!
> >
> >Wassim.
> >e-mail: net2000_at_francemel.com
> >
> >
> >Sent via Deja.com http://www.deja.com/
> >Share what you know. Learn what you don't.
>
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Mon Jun 28 1999 - 20:22:00 CDT