Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
6  
What is the problem with the gaps? An artificial primary key should not have a meaning and thus gaps should be meaningless as well. –  a_horse_with_no_name Jul 19 '11 at 11:40
    
In SQL Server I do things like: select * from #tmp t1 join #tmp t2 on t1.id = td2.id + 1. –  bernd_k Jul 19 '11 at 12:00
2  
Which is bound to fail just as well unless SQL Server guarantees a gapless sequence (and then DELETEs may never occur) –  a_horse_with_no_name Jul 19 '11 at 12:02
    
I think SQL Server identity columns do. But I asked it here dba.stackexchange.com/questions/3948/… –  bernd_k Jul 19 '11 at 12:46
add comment

2 Answers

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               
share|improve this answer
    
A higher sequence number would only be inserted before a lower sequence number if you are using RAC and if the sequence is specified as NOORDER (the default). You can force sequences to be increasing in a RAC environment and they will be increasing in a non-RAC environment assuming we're talking about the order that inserts happened (or triggers fired depending on how you're building the app) and not the order in which transactions are committed. –  Justin Cave Jul 19 '11 at 18:05
1  
@Justin - see example added above. Also IMO "the order in which transactions are committed" means something in an MVCC database, but "the order that inserts happened" does not. –  Jack Douglas Jul 20 '11 at 8:08
add comment

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
share|improve this answer
    
I guess you have to do it the other way round, cache more values than you will use in a given case. But how much is that? –  bernd_k Jul 19 '11 at 12:18
2  
That would mean that you still can't "join" over id + 1 as both sessions would use completely different ranges of the ID. The only reliable way is to use row_number() to generate the gapless IDs "on-the-fly" based on the insertion order. –  a_horse_with_no_name Jul 19 '11 at 12:22
1  
Another thing: Are you sure you need the temp tables? In at least 80% of all cases I have seen the approach with temp tables in Oracle was not needed (when migrating that solution from SQL Server) –  a_horse_with_no_name Jul 19 '11 at 12:23
    
In Oracle I can get rid of the id + 1 self join, as its analytical functions contain lead() and lag(), but that makes the code different for SQL Server and Oracle. And yes this is a problem of the remaining 20%. –  bernd_k Jul 19 '11 at 12:39
3  
@bernd - have you been told to keep your SQL portable between Oracle and SQL Server? They are very different beasts. I'd say you're wasting the money you are spending on Oracle unless you are using its features the way they are intended to be used. –  Jack Douglas Jul 19 '11 at 13:18
show 4 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.