Home » RDBMS Server » Server Utilities » Bulk updates on a table for updating IDing a sequence to a table (Oracle 11)
Bulk updates on a table for updating IDing a sequence to a table [message #637942] Fri, 29 May 2015 23:46 Go to next message
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.
Re: Bulk updates on a table for updating IDing a sequence to a table [message #637946 is a reply to message #637942] Fri, 29 May 2015 23:56 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Since this needs to run for several tables on a daily basis

It appears to me to be a serious design FLAW

why populate with correct date initially?
Re: Bulk updates on a table for updating IDing a sequence to a table [message #637949 is a reply to message #637946] Sat, 30 May 2015 00:05 Go to previous messageGo to next message
Member2014
Messages: 33
Registered: July 2014
Location: USA
Member
We use sql loaders to load data to the tables with data columns, after which we populate these two fields for downstream processing.
These ID's are populated based on a sequence which tells us how many files were received. Its the file_id and record_id values per feed (generated using sequences). Which we preserve and use for other processes.

Any suggestions on doing this though sqlldr or procedures?
Re: Bulk updates on a table for updating IDing a sequence to a table [message #637955 is a reply to message #637949] Sat, 30 May 2015 00:21 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Any suggestions on doing this though sqlldr or procedures?

Why aren't these value known as they are loaded? TWO always comes after ONE; etc.

Periodic COMMIT only makes results slower.

It appears process was designed to make the results slow, inefficient & resource intensive.
Re: Bulk updates on a table for updating IDing a sequence to a table [message #637956 is a reply to message #637949] Sat, 30 May 2015 00:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68038
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It seems we (more precisely Barbara) already answered this kind of question.
Please search in our "Server Utilities" forum (where I move this topic) waiting for her.
In the meantime, you have to complete your post with an example of what you want (input file, target table, current control file, target values...).

Re: Bulk updates on a table for updating IDing a sequence to a table [message #637958 is a reply to message #637942] Sat, 30 May 2015 02:07 Go to previous messageGo to next message
John Watson
Messages: 8639
Registered: January 2010
Location: Global Village
Senior Member
If you use one statement instead of two, you may (approximately) halve the time, redo, and undo needed:

EXECUTE IMMEDIATE 'UPDATE ' || tbl_nm || ' SET id = ' || file_seq,row_id = sequence.NEXTVAL';
Re: Bulk updates on a table for updating IDing a sequence to a table [message #637962 is a reply to message #637949] Sat, 30 May 2015 18:40 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
Member2014 wrote on Fri, 29 May 2015 22:05
We use sql loaders to load data to the tables with data columns, after which we populate these two fields for downstream processing.
These ID's are populated based on a sequence which tells us how many files were received. Its the file_id and record_id values per feed (generated using sequences). Which we preserve and use for other processes.

Any suggestions on doing this though sqlldr or procedures?


You can use CONSTANT and SEQUENCE in your SQL*Loader control file. Any such calculated fields should be listed after all others. There are plenty of examples in the online documentation and this forum. It is better to use a SQL*Loader sequence than a database sequence in your control file, as processing will be faster.
Re: Bulk updates on a table for updating IDing a sequence to a table [message #640847 is a reply to message #637962] Wed, 05 August 2015 19:54 Go to previous message
Member2014
Messages: 33
Registered: July 2014
Location: USA
Member
Thanks all for the help and apologies for the late response.
Previous Topic: EXPDP error
Next Topic: export with sysdate
Goto Forum:
  


Current Time: Fri Nov 26 21:32:20 CST 2021