Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: update/select statement tuning help

Re: update/select statement tuning help

From: Alan <alan_at_erols.com>
Date: Mon, 27 Sep 2004 10:14:34 -0400
Message-ID: <2rqlibF1dp625U1@uni-berlin.de>

"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message news:1096089350.128632_at_yasure...
> 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)
>

You make DEsupported sound like UNsupported. Sometimes it's just not possible to keep up with the very latest versions. Sometimes what you have works perfectly. We just finished upgrading to 8i from 7.3.4, and guess what- we are still alive and working just fine. Do you buy a new car on every model change just because the manufacturer says you really need a new one, and they are not making any improvements to the current model? Sure, eventually you need a new one- but that's when YOU decide you need one- and, more importantly, can afford one. Received on Mon Sep 27 2004 - 09:14:34 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US