Re: update/select statement tuning help

DGK wrote:

> The table MyTable contains around 3,000,000 + records. I have the following
> update statement which takes around 3 hours to execute. Could any body give
> me some information on how to speed up the process? Oracle version 8.1.7.
> Sort_area_size = 50MB. PK=fldy, index on fldx is also availble.
>
>
> UPDATE MyTable t
> SET (
> t.fld1-1,
> t.fld1-2,
> t.fld1-3,
> t.fld1-4,
> t.fld1-5,
> t.fld1-6
> ) =
> (SELECT u.fld2-1
> u.fld2-2,
> u.fld2-3,
> u.fld2-4,
> u.fld2-5,
> u.fld2-6
> FROM MyTable u
> WHERE t.fldx = u.fldy
> );
>
> thanks and regards,
> DGK
Assuming it is patched to 8.1.7.4 and that you already know that it is beginning desupport in just three months ...

1. Gather current statistics with DBMS_STATS
2. Run an explain plan
3. Create or modify indexes as indicated

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