| Sequence increments when it is not being called. [message #571833] |
Sat, 01 December 2012 05:20  |
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 #571836 is a reply to message #571833] |
Sat, 01 December 2012 06:52  |
Solomon Yakobson
Messages: 1399
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 #571850 is a reply to message #571836] |
Sun, 02 December 2012 21:22 |
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
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Members
Search
Help
Register
Login
Home
