Home » SQL & PL/SQL » SQL & PL/SQL » sequence problem? (forms 6i , 11g db)
sequence problem? [message #666938] Sat, 02 December 2017 02:14 Go to next message
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 Go to previous messageGo to next message
John Watson
Messages: 8922
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 #666941 is a reply to message #666939] Sat, 02 December 2017 02:56 Go to previous messageGo to next message
Amjad_1975
Messages: 82
Registered: January 2017
Member
So sir solution is above code as u mentioned .sir its really precious information.

Best regards.

[Updated on: Sat, 02 December 2017 03:11]

Report message to a moderator

Re: sequence problem? [message #666951 is a reply to message #666941] Sat, 02 December 2017 21:44 Go to previous messageGo to next message
Amjad_1975
Messages: 82
Registered: January 2017
Member
Sir same issue I am facing

Thanks
Re: sequence problem? [message #666952 is a reply to message #666951] Sun, 03 December 2017 02:01 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Did you actually read my answer?
Re: sequence problem? [message #666954 is a reply to message #666952] Sun, 03 December 2017 03:48 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Why do you insist on a gapless sequence? What benefit would it bring to your business?

Here's a nice reading for you: here, here; search for "consecutive numbers" for yourself, if you want.

Shortly: most people use an Oracle sequence "as is". Although you can create with the NOCACHE parameter, your application must take care about not failing when fetching its value (or you'll still have a gap). There are "solutions" which deal with such a requirement; even we use one of these; I'm not sure why because - as far as I know - nobody cares about its value, as long as it is unique so there's no real benefit in it. DOes the performance suffer? I don't think so (in our case) because there are thousands of such numbers, yearly. What if there were millions, daily? I guess we'd have a (huge) problem.

I'd suggest you to forget about it, use the Oracle sequence and worry about more important things.
Re: sequence problem? [message #666964 is a reply to message #666954] Mon, 04 December 2017 01:23 Go to previous messageGo to next message
Amjad_1975
Messages: 82
Registered: January 2017
Member
Thanks sir again precious information I had already solved this issue .
Sir what is your recommendations regarding for multiuser application I need your assistance.


Best regards

[Updated on: Mon, 04 December 2017 01:30]

Report message to a moderator

Re: sequence problem? [message #666965 is a reply to message #666964] Mon, 04 December 2017 01:36 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What difference does it make? All users would use the same sequence.
Re: sequence problem? [message #666967 is a reply to message #666965] Mon, 04 December 2017 02:04 Go to previous messageGo to next message
Amjad_1975
Messages: 82
Registered: January 2017
Member
Thanks sir I used in my application

Create sequence ABC start with 1 increment by 1 nocache nocycle;

Thanks

[Updated on: Mon, 04 December 2017 02:05]

Report message to a moderator

Re: sequence problem? [message #666971 is a reply to message #666967] Mon, 04 December 2017 02:41 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
OK; is that a statement, or a question?
Re: sequence problem? [message #666973 is a reply to message #666967] Mon, 04 December 2017 03:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Amjad_1975 wrote on Mon, 04 December 2017 09:04
Thanks sir I used in my application

Create sequence ABC start with 1 increment by 1 nocache nocycle;
Realize that this is a performances killer.


Re: sequence problem? [message #666976 is a reply to message #666973] Mon, 04 December 2017 03:52 Go to previous messageGo to next message
Amjad_1975
Messages: 82
Registered: January 2017
Member
Sir please make it correct and make it alive with some precious example.

Thanks
Re: sequence problem? [message #666977 is a reply to message #666976] Mon, 04 December 2017 03:54 Go to previous messageGo to next message
Amjad_1975
Messages: 82
Registered: January 2017
Member
@littlefoot sir this is a question.
Re: sequence problem? [message #666978 is a reply to message #666977] Mon, 04 December 2017 04:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What is the question?

Re: sequence problem? [message #666979 is a reply to message #666978] Mon, 04 December 2017 04:09 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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 #666980 is a reply to message #666979] Mon, 04 December 2017 04:18 Go to previous messageGo to next message
Amjad_1975
Messages: 82
Registered: January 2017
Member
Ok thanks sir
Re: sequence problem? [message #666981 is a reply to message #666979] Mon, 04 December 2017 04:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

In addition, gapless is not gapless.
Imagine:
Session 1 takes a number -> 51 (for instance).
Session 2 takes a number -> 52.
Session 3 takes a number -> 53.
Session 1 commits.
Session 2 rolls back.
Session 3 commits.
What's inside the target (table or other)? 51 and 53 without 52.

Re: sequence problem? [message #666984 is a reply to message #666981] Mon, 04 December 2017 04:51 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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 #666991 is a reply to message #666984] Mon, 04 December 2017 06:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Just wanted to warn OP that "nocache" is not a magic solution to no gap.
(And if he can accept gaps with nocache then he can accept any gap and "nocache" is useless otherwise...)

Re: sequence problem? [message #666992 is a reply to message #666978] Mon, 04 December 2017 06:35 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Michel Cadot wrote on Mon, 04 December 2017 04:04

What is the question?

What is the business requirement that makes you believe that you must have gapless sequences?
Re: sequence problem? [message #666994 is a reply to message #666992] Mon, 04 December 2017 07:30 Go to previous messageGo to next message
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 #667120 is a reply to message #666994] Fri, 08 December 2017 04:33 Go to previous messageGo to next message
uzair.awan
Messages: 2
Registered: December 2017
Location: Pakistan
Junior Member
Actually 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
Re: sequence problem? [message #667122 is a reply to message #667120] Fri, 08 December 2017 04:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As this has been said this:
1/ Does not guarantee no gap
2/ Is a real performances killer

Re: sequence problem? [message #667124 is a reply to message #667120] Fri, 08 December 2017 06:18 Go to previous message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
uzair.awan wrote on Fri, 08 December 2017 04:33
Actually 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.
Previous Topic: How to convert column to rows
Next Topic: Temporary LOBs, temp space, PGA
Goto Forum:
  


Current Time: Thu Mar 28 12:12:32 CDT 2024