Re: update of a big table - use index or not?

On 10 Aug., 20:22, Steve Howard <stevedhow..._at_gmail.com> wrote:
> On Aug 10, 9:14 am, ciapecki <ciape..._at_gmail.com> wrote:
>
>
>
> > hi,
>
> > I need help to decide what is better.
>
> > I have a tableA which has id primary_key, and id_2 which is not unique
> > and can be NULL,
> > I have altered the tableA adding column colC, colD, colE
> > now I want to update colC, colD, colE with the values from a very big
> > table tableB which has id_2 as primary key,
>
> > update tableA a set
> > (colC, colD, colE)
> > =
> > (select colC, colD, colE from tableB b
> > where b.id_2 = a.id_2
> > )
> > where a.id_2 is not null;
>
> > I tried to:
> > create index bt_tableA_id_2 on tableA (id_2) compute statistics;
> > and then run the above statement,
>
> > and I have a feeling this runs faster then without an index.
>
> > but still somehow slow :)
>
> > can anybody suggest better solution,
> > I did not try to create new table joing those 2 with left outer join.
> > Would it speed up.
> > Since the query runs sometimes over 1hr it is difficult to test,
> > that's why in the meantime I wanted to gather some more information
> > from much wiser than me :)
>
> > thanks
> > chris
>
> Since it sounds like a one time shot, how large is table A? Sometimes
> (and I am guilty of this) we spend more time tuning the query than it
> would take to run the crummy one and be done with it :)

tableA is about 100k records
tableB is about 4Mio records

BTW. asking DBA to grant me appropriate privs. in my case would be waiting for the more detailed question, why I need it etc. more than a week maybe even a month.

chris Received on Sat Aug 11 2007 - 06:58:37 CDT