Question on Bulk Update

[ronnie]

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

[pando]

what´s wrong using a single update for a 200000 rows table....?

[irehman]

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

[ronnie]

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

[pando]

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

[marist89]

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:

update table_a a
   set a.col3 = a.col1 + a.col2,
       a.col4 = a.col3 + a.col7,
       a.col34 = a.col69 + a.col54;

[slimdave]

can you not do multiple updates at once, rather than waiting for one to finish before starting another?

[irehman]

Just create a PL/SQL procedure.

[slimdave]

Originally posted by irehman
Just create a PL/SQL procedure.

Uh oh, now you've said it.

[chrisrlong]

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