What is the best way to update a large table with a sequence

[commatom]

I have a partitioned table with about 500 million rows.

We need to add am artificial key to the table.

What would you think would be the fastest way?

I know I can just do it in a cursor but I would think that would take forever.

Any suggestions?

 

[Thargy]

Since your post mentions an artificial key, I assume you mean a surrogate key. I further assume that you want an integer value.

The answer is to do a Create Table As Select and use the pseudo column rownum to provide the key.

If you use the dept table in the example SCOTT schema, the following works:-

CREATE TABLE DEPT_WITH_KEY
AS
SELECT DEPTNO, DNAME,LOC,ROWNUM NEW_KEY
  FROM DEPT;

Basically you create the new table by selecting everything from the existing one and including ROWNUM. Once this has succeeded,drop the original table, and rename the new one to be the same name as the original.

Original table is

DEPTNO,DNAME,LOC
10,ACCOUNTING,NEW YORK
20,RESEARCH,DALLAS
30,SALES,CHICAGO
40,OPERATIONS,BOSTON

and the new table is

DEPTNO,DNAME,LOC,NEW_KEY
10,ACCOUNTING,NEW YORK,1
20,RESEARCH,DALLAS,2
30,SALES,CHICAGO,3
40,OPERATIONS,BOSTON,4

Regards

T

 

[taupirho]

Why do you need to add an artificial key. Can you explain a bit more about what the business and/or technical requirment for this is? If you absolutely need to do this then Thargs answer (or a variation of it) is the way to go.

But as the illustrious Mr Kyte would say:

The fastest way to do something is ... not to do it in the first place.


In order to understand recursion, you must first understand recursion.

 

[pete91z]

alternatively, if space is an issue and your partitions aren't too big individually, you add the new surrogate key column to the table, create a new empty table with the same columns and then do a partition exchange to the new table. You could then use a combination of cursors, varrays/bulk collects and forall loops with a sequence to add the new key. Finally, partition exchange it back into the master table. All depends on maintenance slots I guess

 

[ChrisHunt]

Well, you could try doing exactly what your question implies:

UPDATE my_table
SET my_column = my_sequence.NEXTVAL

That may well run faster than a cursor, but the problem you're likely to run into will be running out of rollback space.

I'd probably go with the cursor approach, committing periodically as I go, rather than trying to get the whole thing into one transaction.

If might take more time to test out all the different ways of doing it than it takes to do it in the least efficient way!

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd