-
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.
-
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
-
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
-
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...
-
After the update, you need to export and import the table to avoid row chaining.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|