update of a big table - use index or not?

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 Received on Fri Aug 10 2007 - 08:14:41 CDT