Home » SQL & PL/SQL » SQL & PL/SQL » Sequence increments when it is not being called. (Oracle9i WindowsXP)
Sequence increments when it is not being called. [message #571833] Sat, 01 December 2012 05:20 Go to next message
Eric Langager
Messages: 13
Registered: April 2004
Location: Beijing, China
Junior Member
Greetings,
I have a table called "Subjects" which lists subjects to match with notations in another table I have created a simple sequence (CREATE sequence subjectid) to created the subject id for the table. But I notice that if there is a skip in the date, the sequence increments automatically when I am not even using it. It even appears to be incrementing even when I am not doing any database activity. This is not an issue of data integrity, because the values in the subject_id column do not need to be sequential, they just need to be unique. But it really has me curious. I created another table called "keep_track" to keep track of what is happening:

16-NOV-12 2952
16-NOV-12 2953
16-NOV-12 2954
16-NOV-12 2955
16-NOV-12 2956
16-NOV-12 2957
16-NOV-12 2958
16-NOV-12 2959
16-NOV-12 2960
16-NOV-12 2961
16-NOV-12 2962

DATE_ENTE SUBJECT_ID
--------- ----------
16-NOV-12 2963
16-NOV-12 2964
16-NOV-12 2965
16-NOV-12 2966
16-NOV-12 2967
16-NOV-12 2968
16-NOV-12 2969
16-NOV-12 2970
16-NOV-12 2971
16-NOV-12 2972
16-NOV-12 2973

DATE_ENTE SUBJECT_ID
--------- ----------
16-NOV-12 2974
16-NOV-12 2975
16-NOV-12 2976
16-NOV-12 2977
16-NOV-12 2978
16-NOV-12 2979
16-NOV-12 2980
16-NOV-12 2981
16-NOV-12 2982
16-NOV-12 2983
16-NOV-12 2984

DATE_ENTE SUBJECT_ID
--------- ----------
16-NOV-12 2985
16-NOV-12 2986
16-NOV-12 2987
19-NOV-12 3006
19-NOV-12 3007
19-NOV-12 3008
19-NOV-12 3009
19-NOV-12 3010
19-NOV-12 3011
19-NOV-12 3012
19-NOV-12 3013

DATE_ENTE SUBJECT_ID
--------- ----------
19-NOV-12 3014
19-NOV-12 3015
19-NOV-12 3016
19-NOV-12 3017
19-NOV-12 3018
19-NOV-12 3019
19-NOV-12 3020
19-NOV-12 3021
19-NOV-12 3022
26-NOV-12 3026
26-NOV-12 3027

DATE_ENTE SUBJECT_ID
--------- ----------
26-NOV-12 3028
26-NOV-12 3029
26-NOV-12 3030
28-NOV-12 3031
28-NOV-12 3032
30-NOV-12 3046
30-NOV-12 3047
30-NOV-12 3048
30-NOV-12 3049
30-NOV-12 3050
30-NOV-12 3051

DATE_ENTE SUBJECT_ID
--------- ----------
30-NOV-12 3052
30-NOV-12 3053
30-NOV-12 3054
30-NOV-12 3055
30-NOV-12 3056
30-NOV-12 3057
30-NOV-12 3058
30-NOV-12 3059
30-NOV-12 3060
30-NOV-12 3061
30-NOV-12 3062

I would appreciate any comments about this. -- Eric Langager Beijing, China
Re: Sequence increments when it is not being called. [message #571835 is a reply to message #571833] Sat, 01 December 2012 06:43 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
Hello,

Maybe someone somewhere is calling that sequence (or maybe it is updated inside a trigger) and you are not aware of it.

Regards,
Dariyoosh

[Updated on: Sat, 01 December 2012 06:48]

Report message to a moderator

Re: Sequence increments when it is not being called. [message #571836 is a reply to message #571833] Sat, 01 December 2012 06:52 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2028
Registered: January 2010
Senior Member
Sequence guarantees uniqueness, not consecutive values. By default sequence is created with cache of 20. If you ask for one sequence value Oracle gets 20, caches them and gives you first value from cache. Next time you ask for a value Oracle gives you next value from cache. When you ask for a value and all 20 values are used, Oracle caches another set of 20. This improves performance since sequence generator is called only once for 20 values rather than 20 times. However, unused sequence cache values are lost when database is shut down. As a result you will see gaps. But even if you create sequence without a cache you are not quaranteed to have consecutive values. Session can request sequence nextval and then rollback or simply fail. Anyway, as I said, sequence guarantees uniqueness, not consecutive values.

SY.
Re: Sequence increments when it is not being called. [message #571840 is a reply to message #571836] Sat, 01 December 2012 08:18 Go to previous messageGo to next message
BlackSwan
Messages: 22786
Registered: January 2009
Senior Member
>(Oracle9i WindowsXP)
both are unsupported & obsoleted
Consider joining the 21st Century at your earliest convenience
Re: Sequence increments when it is not being called. [message #571850 is a reply to message #571836] Sun, 02 December 2012 21:22 Go to previous messageGo to next message
Eric Langager
Messages: 13
Registered: April 2004
Location: Beijing, China
Junior Member
Thanks, Friends. Actually the version doesn't matter that much...this database is just for some personal data re: my research at the National Library of China. I always preferred 9i because I was a trainer, and it had the Enterprise Manager, which is very handy for teaching purposes. Not that big an issue here in China, because Chinese kids tend to be pretty good programmers. But when I was teaching at the software college in Arizona I had students who had never written any kind of code and didn't know what a computer program was.

Anyway, the version doesn't matter that much. But I did not know about the cache issue. That has to be what's doing it. There are no triggers in this schema, and I am the only user. I went back and checked and there does seem to be a skip if I leave before using the 20 cached sequences...still some unexplained skips, but I'm not going to worry about it, because the whole thing is a non-issue relative to data integrity. It was just my curiosity that was killing me, not any sort of data problem. This values in this column only need to be unique. I have another PL/SQL routine I use when I need the values to be sequential.

Thanks, all of you, for your kind assistance. -- Eric
Re: Sequence increments when it is not being called. [message #571851 is a reply to message #571850] Sun, 02 December 2012 22:01 Go to previous messageGo to next message
BlackSwan
Messages: 22786
Registered: January 2009
Senior Member
notice the sequence gaps also are concurrent with date gaps.
notice that the size of the gap is never more than 20.
I suspect the system was shutdown or rebooted, then any sequences that had been in cache were tossed into the bit bucket & lost forever.
Re: Sequence increments when it is not being called. [message #571875 is a reply to message #571850] Mon, 03 December 2012 05:50 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2028
Registered: January 2010
Senior Member
Eric Langager wrote on Sun, 02 December 2012 22:22
But I did not know about the cache issue.


As I already mentioned even without cache sequence is not guaranteed to produce consecutive values.

SY.
Re: Sequence increments when it is not being called. [message #571967 is a reply to message #571875] Tue, 04 December 2012 23:44 Go to previous messageGo to next message
dude4084
Messages: 213
Registered: March 2005
Location: Mux
Senior Member
is there any way to control cache size?
Re: Sequence increments when it is not being called. [message #571968 is a reply to message #571967] Tue, 04 December 2012 23:47 Go to previous messageGo to next message
BlackSwan
Messages: 22786
Registered: January 2009
Senior Member
>is there any way to control cache size?
yes

are you unwilling or incapable to Read The Fine Manual?
Re: Sequence increments when it is not being called. [message #571970 is a reply to message #571968] Wed, 05 December 2012 00:14 Go to previous messageGo to next message
dude4084
Messages: 213
Registered: March 2005
Location: Mux
Senior Member
Dear BlackSwan, I really appreciate your knowledge, wisdom, quickness and your time for this forum.

Thank you
Re: Sequence increments when it is not being called. [message #571974 is a reply to message #571970] Wed, 05 December 2012 01:14 Go to previous message
Michel Cadot
Messages: 59111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database SQL Reference

Regards
Michel
Previous Topic: SQL Syntax Error
Next Topic: Efficient way of retrieving - Joining Vs In line view
Goto Forum:
  


Current Time: Wed Sep 17 11:30:25 CDT 2014

Total time taken to generate the page: 0.12629 seconds