Bulk updates on a table for updating IDing a sequence to a table [message #637942] |
Fri, 29 May 2015 23:46 |
|
Member2014
Messages: 33 Registered: July 2014 Location: USA
|
Member |
|
|
Hi,
I have a requirement to update data for two columns in a table which has about 500 million records.
Column 1 - is like a file ID which will be the same for all records (constant)
Column 2 - Is like assigning a rownun. 1,2,3..etc.
Since this needs to run for several tables on a daily basis I put the statements in a procedure and passing the table name as a parameter.
EXECUTE IMMEDIATE 'UPDATE ' || tbl_nm || ' SET id = ' || file_seq;
EXECUTE IMMEDIATE 'UPDATE ' || tbl_nm || ' SET row_id = sequence.NEXTVAL';
Since its runs as a single transaction its taking up a lot of tmp space and lot of time.
Can someone please help me with a better way to do it. I tried writing a cursor with a dynamic SQL to generate update state for every record and commit every 10K records. Which is taking long too.
Any help is appreciated.
Thanks.
|
|
|
|
|
|
|
|
|
|