Home » SQL & PL/SQL » SQL & PL/SQL » Skip in sequence
Skip in sequence [message #236860] Fri, 11 May 2007 03:03 Go to next message
Mauragon
Messages: 20
Registered: February 2007
Location: Philippines
Junior Member
I encountered an error in using sequences here's the code I used in creating it :

CREATE SEQUENCE inv.pal_barcode_tag_number INCREMENT BY 1 START WITH 300000 MAXVALUE 999999;

I also created a synonym for the sequence

CREATE SYNONYM pal_barcode_tag_number FOR inv.pal_barcode_tag_number;

Then I used the sequence when inserting a record. the problem is that the sequence skipped from the value of 300002 and it became 300020.

The program was tested in a test instance and it worked fine but in production this occured.

Any help would be appreciated. Thank you.

Additional note: This occurred 3 times already. So it couldn't be just an isolated case

[Updated on: Fri, 11 May 2007 03:44]

Report message to a moderator

Re: Skip in sequence [message #236861 is a reply to message #236860] Fri, 11 May 2007 03:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is the expected behaviour.
Sequence guarantees a different number each time.
It does not guarantee order (unless you give ORDER keyword).
It does not guarantee you get all numbers. In fact, you won't. For instance, you lose numbers when you shut down the instance.

Regards
Michel
Re: Skip in sequence [message #236871 is a reply to message #236860] Fri, 11 May 2007 03:23 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
Quote:
It does not guarantee order (unless you give ORDER keyword).
Only true in RAC.
From the documentation
Quote:
ORDER is necessary only to guarantee ordered generation if you are using Oracle
Database with Real Application Clusters. If you are using exclusive mode, sequence
numbers are always generated in order.


Quote:
Sequence guarantees a different number each time.
What if you are using 'CYCLE'?

Quote:
For instance, you lose numbers when you shut down the instance.
You MAY lose numbers....
Smile

@OP, It sounds like there is a little more to it than the situation as you have described. The most likely scenario that I can think of is that which Michel has already alluded to. By omitting the NOCACHE argument, you are, by default caching 20 values. I'd bet there is a restart of the instance between your call to the seq returning the value of 3000002 and then the next call which then returned 3000020 (or whatever)
The cached values are lost during the shutdown and startup (As Michel said)

And
CREATE SEQUENCE inv.pal_barcode_tag_number INCREMENT BY 1 START WITH 300004 MAXVALUE 999999;

Isn't the code you used. If it was, you wouldn't have got the value of 300002.

HTH


Re: Skip in sequence [message #236875 is a reply to message #236871] Fri, 11 May 2007 03:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the corrections.

I didn't explicitly talk about NOCACHE option because using it MAY Wink lead to SN or SQ lock contention and it is not a good option to choose.

Regards
Michel

Re: Skip in sequence [message #236880 is a reply to message #236875] Fri, 11 May 2007 03:39 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
Fair comment. Cool
Re: Skip in sequence [message #236884 is a reply to message #236860] Fri, 11 May 2007 03:46 Go to previous messageGo to next message
Mauragon
Messages: 20
Registered: February 2007
Location: Philippines
Junior Member
Sorry I posted the wrong command. It should be 300000. It was such because of the patches to the sequence I have to do.

Thanks for all your comments. So should I add ORDER?
Re: Skip in sequence [message #236887 is a reply to message #236884] Fri, 11 May 2007 03:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
So should I add ORDER?

Quote:
if you are using Oracle Database with Real Application Clusters

Regards
Michel
Re: Skip in sequence [message #236892 is a reply to message #236884] Fri, 11 May 2007 03:55 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
I think the main point that Michel was making (correct me if I mis read you Michel) is that a Sequence cannot guarantee and should not be used to guarantee the generation of Sequential/conterminous values. There are actually various discussion on the topic of sequential values. Do a search through the forums (and maybe google)

HTH
Re: Skip in sequence [message #236896 is a reply to message #236892] Fri, 11 May 2007 04:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You're right this is my point.

And the main point in all the discussions is that "there is no real need of sequential continuous values; there is just a misconception of the application".

Regards
Michel
Re: Skip in sequence [message #236897 is a reply to message #236860] Fri, 11 May 2007 04:10 Go to previous messageGo to next message
Mauragon
Messages: 20
Registered: February 2007
Location: Philippines
Junior Member
I understand. I thank you both Michel and Pablolee for your insights
Re: Skip in sequence [message #237000 is a reply to message #236860] Fri, 11 May 2007 10:03 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Since you have only a limit number of values you can use in your sequence, you might want to consider adding nocache to the sequence. If you are slamming in 20 rows a second, nocache would not be a good idea. If you are inserting in a couple a minute, it will make NO difference in performance or locking.
Re: Skip in sequence [message #237004 is a reply to message #237000] Fri, 11 May 2007 10:21 Go to previous messageGo to next message
Alien
Messages: 245
Registered: June 1999
Senior Member
With 28-digit precision (9i) I have not had the pleasure of seeing a sequence fill up yet. Even with skipping in the 100s.

I'd prefer to avoid the contention rather than save a few out of the fazillions

Regards,

Arian
Re: Skip in sequence [message #237006 is a reply to message #237004] Fri, 11 May 2007 10:25 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Alien wrote on Fri, 11 May 2007 10:21
With 28-digit precision (9i) I have not had the pleasure of seeing a sequence fill up yet. Even with skipping in the 100s.

I'd prefer to avoid the contention rather than save a few out of the fazillions

Regards,

Arian


True, except his lowest value is 300000 and hit max value is 999999. It looks like he only has 6 spaces for an id number and speaking from experience, if you machine is going down for backup every night, this can cause problems after a couple of years. If the max value was unlimited, I would have not made the suggestion.
Re: Skip in sequence [message #237008 is a reply to message #237006] Fri, 11 May 2007 10:30 Go to previous messageGo to next message
Alien
Messages: 245
Registered: June 1999
Senior Member
Sorry, but that doesn't make sense to me. Bringing the machine down for backup every night? I'd fix that first, before worrying about sequences.

If his space is limited, nocache might make sense. But it will only be delaying disaster.
Then again, the OP is the only one who knows all the factors involved.
Re: Skip in sequence [message #237012 is a reply to message #236860] Fri, 11 May 2007 10:39 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Agreed, I would use a hot backup through rman and archiving, but if he has lost his sequence cache 3 times then he is either taking down production or flushing cache. I would take care of that, but having nocache on a sequence that is only being used a couple of times a day will cause absolutely no contention. It is a non-issue. If the sequence is being used thousands of times a day, and with only 6 digits allowed it is not, then I would have a cache a lot bigger then 20. I don't want to start a flame war, so this is my last comment on this issue.
Re: Skip in sequence [message #237092 is a reply to message #236860] Fri, 11 May 2007 20:13 Go to previous message
Mauragon
Messages: 20
Registered: February 2007
Location: Philippines
Junior Member
I modified the command and added the NOCACHE option.
Previous Topic: How to referesh materialzed partly
Next Topic: Infinite Cursor Loop
Goto Forum:
  


Current Time: Fri Dec 09 06:05:55 CST 2016

Total time taken to generate the page: 0.07745 seconds