ansaurus

Question

Preferred way to add surrogate key to existing Oracle DB table

Answer 1

+6 A:

I'd do it the following way:

Create the id column, allowing null values
Issue this query:
```
UPDATE  mytable
SET     id = rownum
```
Alter table to add NOT NULL and PRIMARY KEY for the new id column
Create the sequence, seeding it to MAX(id) + 1 and use it for the further inserts.

Quassnoi 2010-02-18 09:18:19

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

Rene 2010-02-18 13:05:44

@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 2010-02-18 13:27:36

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 2010-02-18 14:16:45

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

Quassnoi 2010-02-18 14:19:38

ansaurus

tags:

views:

answers:

Preferred way to add surrogate key to existing Oracle DB table

related questions