| Skip in sequence [message #236860] |
Fri, 11 May 2007 03:03  |
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 #236884 is a reply to message #236860] |
Fri, 11 May 2007 03:46  |
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 #236892 is a reply to message #236884] |
Fri, 11 May 2007 03:55 |
pablolee
Messages: 2882
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 #237000 is a reply to message #236860] |
Fri, 11 May 2007 10:03 |
Bill B
Messages: 1971
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 #237006 is a reply to message #237004] |
Fri, 11 May 2007 10:25 |
Bill B
Messages: 1971
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 #237012 is a reply to message #236860] |
Fri, 11 May 2007 10:39 |
Bill B
Messages: 1971
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
lead to SN or SQ lock contention and it is not a good option to choose.
