-
Question on Bulk Update
Hi,
We have a table with 200 Columns and 200,000 rows.
The Table looks something like this
table_a
col1
col2
col3
I am trying to update col3 with (col1-col2) value for all the 200,000 rows.
whats the most efficient way to do this. please suggest. I need to do a similar thing for atleast 50 columns.
Thanks
Ronnie
ronnie_yours@yahoo.com
You can if you think you can.
-
what´s wrong using a single update for a 200000 rows table....?
-
You can also write a procedure to update and commit after 10000 rows, just in case your rollback segment is small and you can not increase the size of it, but I would write one statement and update it after business hours.
Just a thought
-
Its not running oout of rollback segment. The problem is that it take around 25 minutes per update statement and i need to do 50 similar updates thats a total of 20 Hrs approx.
I am sure that there must be a better and faster way to do a similar thing.
Thanks
Ronnie
ronnie_yours@yahoo.com
You can if you think you can.
-
probably yea, create a copy, truncate the table and do insert /*+ APPEND */ select
but of course if you have 50 updates then you have to create like 50 temporary tables and truncate 50 times, and write 50 insert select where select you need all 200 columns
-
Re: Question on Bulk Update
Originally posted by ronnie
whats the most efficient way to do this. please suggest. I need to do a similar thing for atleast 50 columns.
50 columns of the same table? How about:
Code:
update table_a a
set a.col3 = a.col1 + a.col2,
a.col4 = a.col3 + a.col7,
a.col34 = a.col69 + a.col54;
Jeff Hunter
-
can you not do multiple updates at once, rather than waiting for one to finish before starting another?
-
Just create a PL/SQL procedure.
-
Originally posted by irehman
Just create a PL/SQL procedure.
Uh oh, now you've said it.
-
Originally posted by irehman
Just create a PL/SQL procedure.
Not unless you have to!!!!
But then, that's still better than manually doing 50 separate updates.
As marist89 pointed out, what's the difficulty in updating all the columns at once with a single statement?
- Chris
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
|