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.
Labels: database, design, oracle
