Re: Mass modification of the data of a column

Robert wrote:

> Hi all,
>
> I would like to modify the content of a column in a large table.
>
> The column contains two distinct values : AS or AR
> and each value must be changed to OW (for AS) and RT (for AR).
>
> The table contains about 20 000 000 rows.
>
> I tried to modify the values with a PL/SQL procedure that did nearly this :
> LOOP
> update tablename set col='OW' where col='AS' and rownum < 50000;
> IF (SQL%ROWCOUNT = 0) then
> commit;
> exit;
> end if;
> commit;
> END LOOP;
>
> If I choose a bigger value for the rownum clause, I usually raise the
> "rollback segment to small" error.
> So the procedure is very long to complete (hours or days).
>
> So, I am looking for a quicker way to make these mass modifications...
>
> I has the idea to export the table content to a file, change the values
> then re-import the file. Will it be quicker ? safe ?
>
> Is there a way to execute the update statement without using the
> rollback segment ?
>
> Any other ideas ?
>
> Thanks a lot in advance,
>
> Bobby

Is there an index on "col"?

If not I wonder whether Tom Kyte's trick with function based indexes might not solve the problem of making the index both small and efficient. Something like:

CREATE INDEX test
ON tablename(DECODE(col,'AR','AR','AS','AS',NULL));

Not sure but I'm going to try it later today to see.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)


----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= East/West-Coast Server Farms - Total Privacy via Encryption =---