Preferred way to add surrogate key to existing Oracle DB table

Question

I have to modify an existing table in a Oracle 10g DB with a few thousand records to add a surrogate autonumber key. One way that comes to my mind is to

1. Create a new sequence
2. Create the id column, allowing null values
3. Updating the id column with the sequence
4. Alter table to add "not null" and "primary key" for the new id column

Is there an easier or more efficient way of doing this (or is there some reason why this wouldn't work)?

Suffolk

Answer 1

I'd do it the following way:

1. Create the id column, allowing null values

2. Issue this query:

   UPDATE  mytable
   SET     id = rownum
   

3. Alter table to add NOT NULL and PRIMARY KEY for the new id column

4. Create the sequence, seeding it to MAX(id) + 1 and use it for the further inserts.

Viscera

Hi, why do you use max(id)+1 when you can use a sequence ? update mytable set id=sequence.nextval – Mueller

@Rene: whenever a sequence runs out of the cached values, it is updated which generates a redo log entry. Using rownum is much faster. – Viscera

Quassnoi, I agree with your approach if we're dealing with megarows and I've given it a vote. But it seems like overkill for "a few thousand rows" as stated by the initial question. Both ways will work. – Gono

@Jim: sure they will, though I hardly see an overkill here :) – Viscera