Re: how to update hundreds of big tables

Cursor for loops are not efficient in this case. Mass Updates are more efficient.

Try to look for some indexed column whose values can be divided into intervals, like for example timestamps or sequential primary keys. Using a timestamp column may often be a good idea on tables that are only inserted into because it follows the physical order in which the rows were inserted.

So I split the update into several statements as follows:

Update table set ... = ...
where timestamp < to_date('1997', 'yyyy');

commit;

Update table set ... = ...
where timestamp >= to_date('1997', 'yyyy') and timestamp < to_date('1998', 'yyyy');

commit;

Update table set ... = ...
where timestamp >= to_date('1998', 'yyyy') and timestamp < to_date('1999', 'yyyy');

commit;

Update table set ... = ...
where timestamp >= to_date('1999', 'yyyy');

commit;

/Torben Jeppesen

sbrkic_at_mailexcite.com wrote in message <763r3h$a8h$1_at_nnrp1.dejanews.com>...
>I have to update a big number of Oracle tables efficiently.
>The names of tables and columns to be updated are obtained
>from a cursor and are based on column data type and schema
>names. Dynamic SQL is used to create the update statements.
>10% of tables are bigger than the biggest rollback segment
>in the database. My question is: what is the most efficient
>method to perform this job? Should I use a cursor loop and
>sequentially update column after column commiting after every
>N rows? Or maybe create many processes (or threads) using Pro*C
>and submit these jobs concurrently (how many processes/threads)?
>Please, provide examples if possible.
>
>Thank you!
>Slavisa Brkic
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Sun Dec 27 1998 - 12:41:53 CST