8

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)?

simon
  • 12,666
  • 26
  • 78
  • 113

1 Answers1

8

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.

Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • Hi, why do you use max(id)+1 when you can use a sequence ? update mytable set id=sequence.nextval – Rene Feb 18 '10 at 13:05
  • @Rene: whenever a sequence runs out of the cached values, it is updated which generates a redo log entry. Using `rownum` is much faster. – Quassnoi Feb 18 '10 at 13:27
  • 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. – Jim Hudson Feb 18 '10 at 14:16
  • @Jim: sure they will, though I hardly see an overkill here :) – Quassnoi Feb 18 '10 at 14:19