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

ciapecki 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

Is there a version number and an explain plan created with dbms_xplan anywhere in sight?

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org