Can I implement a 'gapless' identity column in Oracle?

Question

In SQL Server it is so easy to write:

create table #tmp (
    id      integer identity(1,1) primary key,
    message varchar(256)
);

When I try to migrate this to Oracle, I end up with:

CREATE GLOBAL TEMPORARY TABLE tmp(
    id integer primary key,
    message varchar2(256)
) ON COMMIT PRESERVE ROWS;

CREATE SEQUENCE S_TMP_ID START WITH 1;

CREATE OR REPLACE TRIGGER TR_TMP 
BEFORE INSERT ON tmp
FOR EACH ROW
WHEN (new.id IS NULL)
BEGIN
  SELECT S_TMP_ID.NEXTVAL
  INTO   :new.id
  FROM   dual;
END;
/

OK. I get unique, increasing id values. But, when two sessions are using the same global temporary table at the same time, I get gaps in the sequence of my ideas and they start with some arbitrary value.

Any ideas how to create better identity values for a global temporary table?

Answer 1

The only way to guarantee a gapless series (assuming no deletes) is to serialize - almost always a bad idea

With a sequence or identity you cannot assume your series will be gapless as @a_horse mentions - but it looks like you are assuming that even with gaps, there is some relationship between sequence order and insertion order - this is not true either! A higher sequence number could be inserted before a lower. Therefore trying to join "consecutive" rows is a meaningless concept

example:

create sequence seq;
create table t1(id integer);
create table t2(id integer);

--session 1:
insert into t1 select seq.nextval from dual connect by level<1000000;
commit;

--session 2:
insert into t2 select seq.nextval from dual connect by level<1000000;
commit;

result:

select min(id), max(id) from t1 union all select min(id), max(id) from t2;

MIN(ID)                MAX(ID)                
---------------------- ---------------------- 
1                      1671679                
356547                 1999998               

Answer 2

Even though the assumption that auto-generated IDs are always gapless is asking for trouble, you might get away with removing the CACHE attribute from your sequence.

CREATE SEQUENCE S_TMP_ID START WITH 1 NOCACHE

If you need to join to the "previous" row (which sounds very strange, but as we don't get to see the whole picture, I will just accept that) you can use something like this in order to make it robust against gaps:

WITH gapless_tmp1 AS (
   SELECT *, 
          row_number() over (order by id) as gapless_id1
   FROM tmp_1
), 
gapless_tmp2 AS (
   SELECT *, 
          row_number() over (order by id) as gapless_id2
   FROM tmp_2
) 
SELECT *
FROM gapless_tmp1 t1
   JOIN gapless_tmp2 t2 ON t1.gapless_id1 = t2.gapless_id2 + 1