This content has been marked as final. Show 6 replies
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 databasewhat 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.
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 .
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.