very slow update

[7280]

Hi,
i'm issuing an update against two tables with 150.000 records each and this transaction is running since 1 hour.
How to understand what's oracle doing now and how long
will it take?
What exactly happens during an update statement?
Following my update.

update mya p
set(name,lastname,email) =
(select name,lastname,email
from myb s
where p.cod=s.cod)
where exists
(select cod
from mya pp
where p.cod=pp.cod);

This is a test database with few sga allocated.
Could this be the problem?
show sga

Total System Global Area 174471292 bytes
Fixed Size 116860 bytes
Variable Size 108646400 bytes
Database Buffers 65536000 bytes
Redo Buffers 172032 bytes

I have to run this update twice, once with 600.000 records and second time with 1.000.000 records.
I will create an index on the cod column.

 

[SantaMufasa]

Yes, 7280, an index on "cod" should make all the difference in the world. Without the index, it is probably attempting a bad, bad, bad full-table scan for each update.

Dave

 

[dbtoo2001]

You may want to see what it is doing to your rollback segments, too.

Why do you need the exists? Won't the where clause in the select statement limit it to just the ones that exist in mya?

 

[aryeh1010]

SantaMufasa is right about the index.

dbtoo2001's comment is right or wrong, depending on whether the query syntax is correct.

update mya p
set(name,lastname,email) =
(select name,lastname,email
from myb s
where p.cod=s.cod)

this subquery is unneccessary if the table in the sub-query is really mya. It only makes sense if you mean myb, because every record in mya must exist in mya, therefore the sub-query is not needed, as dbtoo2001 pointed out. However not every record in mya has a corresponding record in myb.
where exists
(select cod
from mya pp
where p.cod=pp.cod);

 

[sem]

I'm not sure that you check existing record in correct table. Though if your tables are linked by foreign key (I think you have 1-to-0 relation), you may try to save some resources by

update
(select p.code, p.name,p.lastname,p.email
,s.name sname, s.lastname slastname, s.email semail from mya p, myb s where p.cod=s.cod)
set name=sname, lastname=slastname, email=semail

Regards, Dima