This discussion is archived
6 Replies Latest reply: Dec 20, 2012 12:25 PM by MLBrown RSS

gaps of sequences on deletion

newbi_egy Explorer
Currently Being Moderated
db and dev 10g rel2 , xp sp3
hi all,
i know that when using sequences for inserting in a pk field , i should use the pre-insert trigger for avoiding gaps .

but what happens when delete a record ?
i mean : i have records with numbers ;
50
51
52
when i delete the "51" record , it would be :
50
52
and the next value of the sequence is "53" , and there is a gap between (50 and 52) ,
how to deal with this process ?

thanks
  • 1. Re: gaps of sequences on deletion
    Christian Erlinger Guru
    Currently Being Moderated
    So there is a gap...and what is the problem with that?how about this:create a sequence caching 20 values (default) and flush the shared pool or restart the database... And now see how big this gap is....
  • 2. Re: gaps of sequences on deletion
    newbi_egy Explorer
    Currently Being Moderated
    and what is the problem with that?
    so why do you use pre-insert trigger when insert a record ?!!

    flush the shared pool or restart the database
    what do you mean with this "flush the shared pool" -- i am not american , and how to restart the database ? and what happens then ?

    thanks a lot
  • 3. Re: gaps of sequences on deletion
    Christian Erlinger Guru
    Currently Being Moderated
    so why do you use pre-insert trigger when insert a record ?!!
    Fetch a new value from the sequence?

    >
    flush the shared pool or restart the database
    what do you mean with this "flush the shared pool"
    $[CRONET@asterix1_impl] create sequence test_seq cache 20;
    
    Sequence created.
    
    $[CRONET@asterix1_impl] select test_seq.nextval from dual;
    
       NEXTVAL
    ----------
             1
    
    $[CRONET@asterix1_impl] select test_seq.nextval from dual;
    
       NEXTVAL
    ----------
             2
    
    $[CRONET@asterix1_impl] select test_seq.nextval from dual;
    
       NEXTVAL
    ----------
             3
    
    $[CRONET@asterix1_impl] alter system flush shared_pool;
    
    System altered.
    
    $[CRONET@asterix1_impl] select test_seq.nextval from dual;
    
       NEXTVAL
    ----------
            21
    
    $[CRONET@asterix1_impl]
    -- i am not american , and how to restart the database ? and what happens then ?
    Neither am I, and you surely know how to restart the database?!?

    There surely are gaps if you delete previously populated records from the sequence, but as you can see above you will have gaps in quite a few other cases. If you restart the database you flush the shared pool as the whole allocated memory is released. When I fetch a number from the sequence in a different session then yours you will have gaps. When you rollback your transaction you will have gaps. A sequence doesn't generate gapless numbers by definition. This has been discussed quite often, search the forum and/or asktom on why.

    cheers
  • 4. Re: gaps of sequences on deletion
    newbi_egy Explorer
    Currently Being Moderated
    i understand from your words that gaps are normal thing , and i've not to worry about it at all .

    that's why i asked about the pre-insert trigger issue . i learned that using this trigger with sequences is the best way to avoid gaps , which mean that gaps are not a good thing , and anyone should avoid it ?

    so your words are confliting . that's why i asked my question .

    thanks
  • 5. Re: gaps of sequences on deletion
    Christian Erlinger Guru
    Currently Being Moderated
    I cant see where my words are conflicting,but anyway.a sequence is an object to generate unique numbers which makes it suitable for surrogate keys.use it where ever you find it useful.

    Cheers
  • 6. Re: gaps of sequences on deletion
    MLBrown Journeyer
    Currently Being Moderated
    I agree with Christian - his words are not conflicting. He explained the sequence situation quite well.

    As far as why assign a seqence via PRE-INSERT is because it is beneficial since you are creating the number when you are ready to save the record which helps minimize the gaps. If you were creating the sequence everytime a person started to fill out a record, then you would lose that number everytime the user decided to get out and do something else without saving it first.

Legend

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