Skip to Main Content
  • Questions
  • Strange Oracle ID allocation behaviour

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Michael.

Asked: September 05, 2023 - 2:12 pm UTC

Last updated: September 05, 2023 - 3:08 pm UTC

Version: 19g

Viewed 1000+ times

You Asked

I'll summarise this query with a (hopefully easy to follow) scenario:

We have two tables:
TableOne
TableTwo
Both have identically defined ID columns ("ID" NUMBER GENERATED BY DEFAULT AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE NOT NULL ENABLE).

We perform these actions:
We insert a row into each.
TableOne's highest ID is now 28.
TableTwo's highest ID is now 1620.
We commit.
We wait four months.
We insert a new row into each.
TableOne skips several IDs (ie. the sequence goes from 28 directly to 41)
TableTwo does not skip any IDs (ie. the sequence goes from 1620 to 1621)

And my question is, why do the two tables behave differently?

and Chris said...

I'm guessing your question relates to the missing values (29-40) for the first sequence.

If so, this has to do with the caching of sequence values. When you get the nextval for a sequence, the database automatically generates the next N numbers and caches them. The value of N is whatever you've set CACHE to. (Though in 21c the database is able to auto-tune the number of cached values; so treat this as a minimum rather than a fixed N from that release on).

If you leave the sequence untouched for a while, eventually the database will purge these cached values. You can simulate this by flushing the shared pool.

When you next access the sequence, it'll continue from whatever the last cached value was plus one.

The second sequence doesn't miss any values because you were lucky: 1620 is a multiple of the cache (20). If it had ended on any value that isn't a multiple of 20 it would have skipped values too.

You can see what the next value would be if the cache is lost by querying *_sequences.last_number. For example:

create sequence s;

select last_number, cache_size 
from   user_sequences
where  sequence_name = 'S';
/*
LAST_NUMBER CACHE_SIZE
----------- ----------
          1         20
*/
select s.nextval from dual;
/*
   NEXTVAL
----------
         1
*/
select last_number, cache_size 
from   user_sequences
where  sequence_name = 'S';
/*
LAST_NUMBER CACHE_SIZE
----------- ----------
         21         20
*/      
alter system flush shared_pool;

select s.nextval from dual;
/*
   NEXTVAL
----------
        21
*/


Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database