sequence problem? [message #666938] |
Sat, 02 December 2017 02:14 |
|
Amjad_1975
Messages: 82 Registered: January 2017
|
Member |
|
|
Seniors my problem is I have created sequence successfully
Create sequence xyz start with 1 increment by 1
Problem is i called it on pre-inser trigger which inserting fine
But when I restart my PC and again entering a record serial skip to direct 21
Example inserted before restart my PC
1
2
Now restarted my PC now serial is skip on
21
22
I have never seen problem like this kindly guide me where is the problem.
Best regards
|
|
|
Re: sequence problem? [message #666939 is a reply to message #666938] |
Sat, 02 December 2017 02:22 |
John Watson
Messages: 8931 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
It isn't a problem, it the way the sequence works. The default is
Create sequence xyz start with 1 increment by 1 cache 20 noorder;
So numbers are issued in batches of twenty, and any unused are discarded if you restart the instance.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Re: sequence problem? [message #666979 is a reply to message #666978] |
Mon, 04 December 2017 04:09 |
cookiemonster
Messages: 13922 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
No cache is slower than cache.
Gapless is slower than non-gapless.
If you really absolutely need gapless then you have to live with the fact that it's slower.
|
|
|
|
|
Re: sequence problem? [message #666984 is a reply to message #666981] |
Mon, 04 December 2017 04:51 |
cookiemonster
Messages: 13922 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Well there are ways of doing real gapless, but they all involve serializing access to the table and that's a real performance killer.
Nocache isn't really gapless, it just reduces the frequency of the gaps.
|
|
|
|
|
Re: sequence problem? [message #666994 is a reply to message #666992] |
Mon, 04 December 2017 07:30 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
I have had discussions with people who said that for things like invoice numbers (for example) their local laws specify that there can be NO gaps in the numbering. I explained that a sequence will not work to do this. You have to serialize inserts into the table to do this. This example assumes that the invoice number is indexed and you don't take these steps until you are ready to insert the record. you lock the table and then get out as soon as possible.
1) Lock all updating in the table. It will wait until the table is available to be locked.
LOCK TABLE MY_TABLE IN EXCLUSIVE MODE;
2) get the next sequence number
SELECT MAX(INVOICE_NUMBER) + 1 INTO NEXT_INV FROM MY_TABLE;
3) Insert the new row
INSERT INTO MY_TABLE(INVOICE_NUMBER,...) VALUES(NEXT_INV,...);
4) commit it so other users will see the new invoice number. The commit will also release the exclusive lock.
COMMIT;
All that being said, unless your local law specified that you must have gapless numbers I would always use oracle sequences
[Updated on: Mon, 04 December 2017 07:31] Report message to a moderator
|
|
|
|
|
Re: sequence problem? [message #667124 is a reply to message #667120] |
Fri, 08 December 2017 06:18 |
|
EdStevens
Messages: 1376 Registered: September 2013
|
Senior Member |
|
|
uzair.awan wrote on Fri, 08 December 2017 04:33Actually when you use SEQUENCE with CACHE 20 then 20 numbers from sequence are stored in cache and when system re-starts or database shut-down all those numbers in cache are wasted and on restart of database , SEQUENCE gives you new numbers starting from last wasted integer of cache+1.
To avoid this, simply create your sequence without cache then it will directly write from disk and you will face no gap in sequence.
Regards
M. Uzair Awan
Oracle Developer
+92 (300) 20208754
Isn't that what John Watson said six days ago?
And as Michael Cadot explained on Monday, that still does not guarantee gapless sequences.
|
|
|