How Not to Use Surrogate Keys

There have been plenty of discussions about the use of surrogate keys vs. natural keys. This is not about that.

For some reason lately I've been noticing surrogate keys...a lot. Specifically in analytic functions or in the ORDER BY clause.

Here's an example of it's use in an analytic function:

SELECT
  id,--surrogate key
  col1,
  col2,
  col3,
  ROW_NUMBER() OVER ( PARTITION BY col1 ORDER BY id ) my_rownum
FROM some_table;

OK, maybe I should have said anywhere you see an ORDER BY clause. Shoot me.

Like I was saying, don't use them in the ORDER BY clause.

The natural assumption (yes I know about those), assuming (again, I know) it's done via a sequence, is to assume (man...) that the highest number was the last one created. Not necessarily.

What if you have a 2 node RAC Cluster?

CREATE TABLE t
( 
  id NUMBER, 
  created TIMESTAMP DEFAULT SYSTIMESTAMP
);

CREATE SEQUENCE t_seq
  START WITH 100
  CACHE 100
  INCREMENT BY 1
  NOCYCLE;

In my tnsnames.ora file, I created an entry for each node (as opposed to have each ADDRESS in one entry).

I connect to node 1 and run the following:

INSERT INTO t ( id ) VALUES ( t_seq.nextval );

I then connect to node 2

INSERT INTO t ( id ) VALUES ( t_seq.nextval );
COMMIT;

And back to node 1

INSERT INTO t ( id ) VALUES ( t_seq.nextval );
COMMIT;

A simple SELECT

SELECT * FROM t;

    ID CREATED
------ --------------------------------
   100 02-FEB-09 07.19.20.786144 PM
   101 02-FEB-09 07.19.32.562516 PM
   200 02-FEB-09 07.19.25.100219 PM

Now let's order it by CREATED:

SELECT * FROM t ORDER BY created;

    ID CREATED
------ -------------------------------
   100 02-FEB-09 07.19.20.786144 PM
   200 02-FEB-09 07.19.25.100219 PM
   101 02-FEB-09 07.19.32.562516 PM

Lesson? Don't rely on the sequence generated surrogate key to be in ORDER of creation.

Update: I've used the surrogate many times in these types of cases. I suppose the only "reliable" place to use the surrogate key would be in the child table (where the surrogate key is a foreign key and has "some" value). I've just now realized it and will move away from it going forward.