OraFAQ Forum: Server Utilities » Bulk updates on a table for updating IDing a sequence to a table

Home » RDBMS Server » Server Utilities » Bulk updates on a table for updating IDing a sequence to a table (Oracle 11)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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.

Report message to a moderator

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

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?

Report message to a moderator

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

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?

Report message to a moderator

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

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.

Report message to a moderator

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

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

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...).

Report message to a moderator

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

John Watson
Messages: 8966
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';

Report message to a moderator

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

Barbara Boehmer
Messages: 9104
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.

Report message to a moderator

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

Member2014
Messages: 33
Registered: July 2014
Location: USA

Member

Thanks all for the help and apologies for the late response.

Report message to a moderator

Previous Topic:	EXPDP error
Next Topic:	export with sysdate

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Mar 03 16:22:13 CST 2025