Re: Update statement taking a long time

jmaruti_at_gmail.com wrote in
news:1122480546.988342.250420_at_z14g2000cwz.googlegroups.com:

> I am trying the following update:
>
> update target a
> set B.col3 =
> ( select O.col3
> from lookup O
> where a.key1 = O.key1
> and a.key2 = O.key2);
>
> Create table target (key1 char not null, key2 number not null, col3
> number);
>
> alter table target add constraint target_pk primary key (key1, key2);
>
> Create table lookup
> (key1 char not null, key2 number not null, col3 number not null);
>
> alter table target add constraint lookup_pk primary key (key1, key2);
>
> target table has 9000 records and lookup has 1500000 records.
>
> The above update takes a long time to run and I have not been able to
> complete it.
>
> But the following update takes around 2 secs to run:
>
> UPDATE (SELECT a.col3 a1, b.col3 b1 FROM target a, lookup O WHERE a.
> key1 = b.key1 AND a.key2 = b. key2)
> SET a1 = b1;
>
> I want to know whey the first statement run for more than 45 mins and
> still did not finish?
>
> If any more info is needed, please let me know.
>
>

ALTER SESSION SET SQL_TRACE=TRUE; Why ask us when you can answer your own question? What is happening during those 45 minutes? The resultant trace file will contain the answer. Received on Wed Jul 27 2005 - 20:53:31 CDT