Populating a primary key column

[una]

Hi:

I have a table that has 3 million rows. The table does not currently have a primary key defined. I want to add an ID column which will contain an increasing sequential number and use it for the primary key. I was wondering what will be the quickest way to populate this column.

Thanks,

Una.

[pando]

probably a PL/SQL procedure

something like

a for loop which loops 3 million times
and update table set x=yy where rownum=index of for loop

[Paolo Polverari]

Pando ,
you're right but I think probably there's no need of PL/SQL

I found this
(from Oracle 8i SQL Reference
2 - Basic elements of Oracle SQL)

...
..
"You can also use ROWNUM to assign unique values to each row of a table, as in
this example:
UPDATE tabx
SET col1 = ROWNUM;"
..
..

I hope this is a solution

Regards

[pwoneill]

I wonder if that would work for a sequence.nextval, I would guess it does. My databases are down or I'd try it. A sequence is probablly more what you're looking for as opposed to rownum.

update table set newkey = newsequence.nextval;

test it out on a test table first to see that it actually gives each row an increasing number instead of all the same.

edit
--

er, wait, i suppose rownum would work just the same then you start the future sequence at the max value or maybe you can just keep inserting the rownum...

[tamilselvan]

After the update, you need to export and import the table to avoid row chaining.