|
Home > Archive > Oracle Server > July 2005 > Update statement taking a long time
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Update statement taking a long time
|
|
| jmaruti@gmail.com 2005-07-27, 11:23 am |
| 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.
| |
| IANAL_VISTA 2005-07-27, 8:23 pm |
| jmaruti@gmail.com wrote in
news:1122480546.988342.250420@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.
|
|
|
|
|