Skip to Main Content
  • Questions
  • "Gapless" sequence using a table of sequence values



Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Tom Kyte

Thanks for the question, Dieter.

Asked: September 03, 2008 - 2:11 am UTC

Answered by: Tom Kyte - Last updated: September 04, 2008 - 8:45 am UTC

Category: Database - Version: 10.2.0

Viewed 1000+ times

Whilst you are here, check out some content from the AskTom team: Converting Common Table Expressions from SQL Server to Oracle

You Asked

Ti Tom,
I especially loved all of your postings and comments regarding the need (or rather not in your opinion) of gapless sequences but unfortunately there is one article you wrote I'm no longer able to find. You explained how to build a gapless sequence by using a physical table of prefilled sequences and a method to mark them as reserved and used.
Unfortunately I'm no longer able to find this article and I cannot really remember where I read it except that it was from you.
Thank you for your help and I'm really looking forward to hear you at the Oracle Open World in a few weeks,

and we said...

I don't recall ever writing such a thing, not saying it isn't out there - I just don't recall. And if I did, it is flawed. The only way - the ONLY way - to have a gapless sequence is to use a serial operation:

create table bad_idea( name varchar2(30) primary key, value number not null ) organization index;

insert into bad_idea (name,value) values ( 'my_seq', 0 );

and then to get a sequence:

update bad_id set value = value+1 where name = ? returning value into ?;

A table based approach would easily allow for gaps. Say three transactions are going and we have the values 1..infinity loaded into the table.

transaction 1 gets value 1
transaction 2 gets value 2
transaction 3 gets value 3

transaction 1 and 3 commit
transaction 2 rolls back due to an error

you are at the end of the month (no one is asking for another sequence right now) and closing the books.

uh oh. gap....

and you rated our response

  (1 rating)

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


Perhaps an AQ...

September 03, 2008 - 9:19 pm UTC

Reviewer: Warwick Sands from Sydney, NSW

Hi Tom,

I remember the topic, but couldn't find it either. From memory, another respondent suggested using a "non-supported" feature something like:

SELECT value FROM some-table FOR UPDATE skip locked

As I remember it the final thoughts were to create a number of sequence values and then insert these into an AQ.

The calling process would retrieve the next-sequence-value from the queue. If the user rolled-back then the value would be returned into the queue.

I haven't tried this but it sounded promising at the time.

Love the site. Thank you and looking forward to seeing you at the Gold Coast conference in October.


Tom Kyte


September 04, 2008 - 8:45 am UTC

but you see the 'bug' waiting to happen there right. It is NOT GAP FREE.

the end of month processing would easily see gaps, easily. And therein lies the rub - you are back to not being gap free, hence it will not work.

do not use skip locked, it works in a very special way with a subset of SQL and is ignored in many cases (eg: it doesn't work the way you think it should, it works the way it needs to work for AQ)