Re: HELP :Updating a table with 3 million records...

Kris :

It is very simple to do , There are two ways of doing it

1. Whenever you update any huge table it takes much longer than if you were to create another table as select and performing the operation on the data while creating the new table

so 1st METHOD : create table backup ( column listings ) as select column1, ltrim(column2), rtrim(column3) etc etc from mastertable;

This will be very fast.

2nd Method :

I update huge tables like SO_LINES_ALL which have 8-9 mill rows in just about less than 2 hours.

The trick here is to use the ROWIDS to parallelize the DML operation

ie UPDATE TABLE_NAME set column_name = rtrim(column_name) where rowid between substr(rowid,1,6) = 'A' [ Using blocks part of the ROWID ] By using this method you can achieve degrees of parallelism to 16

Hope this helps , If this does not work let me know

my email is agidwani_at_cisco.com

Kris wrote:
>
> Hi All,
>
> We are running into a serious problem. We hae a table with 3 million records
> and we have to do a ltrim , rtrim on one of the columns. I tried a simple
> update statement and it took ages. I aborted and decided to run using a
> PL/SQL script.
>
> I loaded the 3 million in a cursor and updated the record and commited. This
> again is taking more than 23 hours and has finished around 2 million
> records. Now I am rewriting my script to load the records that have not been
> trimmed and update them, it is the same scenario, takes ages.
>
> I tweaked my init.ora parameter to a large database. Unfortunately my
> db_block_size is 2k. We are running oralce8.0 on NT with 256M and dual
> processor ....
>
> Any suggestions to speeden the process. Any help would be greatly
> appreciated.
>
> Krishnan
> e-mail:krishnan_at_cdgpd.com
Received on Fri Jan 08 1999 - 08:33:37 CST