database design - Oracle Sequence Gaps: Are Skipped Values Ever Reused - Stack Overflow

admin管理员组

文章数量:1292285

I use an Oracle sequence of the form:

CREATE SEQUENCE MY_SEQUENCE INCREMENT BY 1 START WITH 1000000000 MAXVALUE 9999999999 MINVALUE 1000000001 
NOCYCLE CACHE 20 NOORDER;

This sequence is used in an Oracle function to generate new values. The generated value is extended by a 2-digit prefix and a 1-digit check digit. The result is a 13-digit number sequence

If several of these number sequences are generated in a row by the program, there are, as expected, small gaps due to the check digit.

If the function is called in parallel, there are often large jumps, for example 1st number sequence 9330012274498 2nd number sequence 9330012582494 ">" difference 307.996

My question is, are the sequence values that have not yet been used but lie between the sequences of the two generated number sequences are still used, since no value has been retrieved from them?

In the above case, we are talking about sequence values between 3001227450 (= 3001227449+1) and 3001258248 (= 3001258249-1)

Or are these 30,798 sequence values lost?

I use an Oracle sequence of the form:

CREATE SEQUENCE MY_SEQUENCE INCREMENT BY 1 START WITH 1000000000 MAXVALUE 9999999999 MINVALUE 1000000001 
NOCYCLE CACHE 20 NOORDER;

This sequence is used in an Oracle function to generate new values. The generated value is extended by a 2-digit prefix and a 1-digit check digit. The result is a 13-digit number sequence

If several of these number sequences are generated in a row by the program, there are, as expected, small gaps due to the check digit.

If the function is called in parallel, there are often large jumps, for example 1st number sequence 9330012274498 2nd number sequence 9330012582494 ">" difference 307.996

My question is, are the sequence values that have not yet been used but lie between the sequences of the two generated number sequences are still used, since no value has been retrieved from them?

In the above case, we are talking about sequence values between 3001227450 (= 3001227449+1) and 3001258248 (= 3001258249-1)

Or are these 30,798 sequence values lost?

• oracle-database
• database-design
• sequence

Share Follow edited Feb 13 at 10:19 Till Glöckner asked Feb 13 at 9:28 Till GlöcknerTill Glöckner 4588 bronze badges 1

• A sequence is a simple counter, it doesn't keep records of what values have or have not been used, it merely knows what value to assign next. – Álvaro González Commented Feb 13 at 12:47

Add a comment  | 

3 Answers 3

Sorted by: Reset to default 3

Yes, the values between two generated sequence values may be generated in the future. (Although this is not exactly the same thing as being "reused", since they were never "used" in the first place.)

This behavior is most frequently observed when using Oracle Real Application Cluster, where each instance will have a separate set of cached sequence values. Instance 1 may generate the numbers 21, 22, 23 at 10AM, and then instance 2 may generate the numbers 1, 2, 3 at 11AM.

As the other answers explain, sequences are guaranteed to be unique, but they are not guaranteed to be gapless or ordered, especially with the NOORDER option.

In these days of 64-bit computing, numbers are a (near) limitless resource. Why worry about what's "lost" ? All that matters and the whole point of sequences is to provide a unique surrogate key to identify a row. In some cases (particularly when we lack a metadata date that can be used to identify row age), it can also important that newer values always be higher than older values, and sequences can guarantee that too. But gaps are inescapable and irrelevant.

If you did want to reduce the gaps for some reason, you can set NOCACHE. It's the caching that's primarily responsible for the gaps. It's a session cache, not a system-cache. Each session maintains its own sequence cache memory individually. When sessions call for a sequence value the first time, they get allocated in their cache however many values CACHE is set to (e.g. the next 20 values), while only using the first of them. If they then ask for another, they don't need to revisit the sequence - they have another 19 to use up before having to read the sequence again. But if they don't need those 19, then that session moved the sequence ahead by 20 but only used 1. Hence you see 19 "missing" values in your data. This gets significantly magnified when using parallelism, as each parallel thread does their own caching, so a single parallel insert can leave quite a few gaps. If you set NOCACHE then a session won't use up sequence values it doesn't intend to use, but that still doesn't guarantee a lack of gaps, because code may fail on the insert of that one row, leaving it out of the table but still having moved the sequence ahead. But it will vastly reduce the amount and size of the gaps, to be sure.

But be warned: if you do set the sequence to NOCACHE, and the sequence is hit with high frequency, it will incur a high overhead cost and severely impact your insert performance. It's simply not worth it just to keep the number lower, unless you have a 32-bit limitation somewhere in old client code into which these values will eventually get read and are in danger of overflow (@ 2.1 billion for signed integers). That's rarely an issue these days.

No those values are not "reused". Sequences guarantee a unique number, they do not guarantee no gaps. You could write your own function as explained in this excellent video by Connor McDonald. But you probably won't unless there is an extremely good reason. This blog is interesting as well. Especially the statement "Gaps exist because it is not worth the huge effort to avoid them.". Just think what the effort would be to reuse the skipped values...

本文标签： database designOracle Sequence Gaps Are Skipped Values Ever ReusedStack Overflow