Implement a ‘gapless’ identity column in Oracle

identityoracle

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?

Best Answer

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
Related Question