| Slow Query [message #4308] |
Tue, 26 November 2002 09:34  |
Jay
Messages: 127
Registered: October 1999
|
Senior Member |
|
|
I am running a simple update query that is updating a large table. I ran this query over the weekend, and it wasn't finished on Monday morning? Would it be faster if I took out the last 2 lines, or is there a more efficiant way to run this?
update act
set full_name = (select full_name from actname
where actname.cnumber = act.cnum)
where act.cnum in
(select cnumber from actname)
|
|
|
|
| Re: Slow Query [message #4311 is a reply to message #4308] |
Tue, 26 November 2002 10:39  |
ctg
Messages: 146
Registered: July 2002
|
Senior Member |
|
|
The problem might be that you are using a correlated subquery in your SET clause. So try putting it into a pl/sql pgm. This will cause multiple updates to execute, but you will get the same rows updated.
DECLARE
CURSOR act_cur IS
SELECT full_name, cnumber
FROM actname
ORDER BY cnumber;
BEGIN
FOR act_rec IN act_cur LOOP
UPDATE act
SET full_name = act_rec.actname
WHERE cnum = act_rec.cnumber;
-- COMMIT;
END LOOP;
--COMMIT;
END;
/
you would need to decide where to put the commit.
hope this helps
|
|
|
|
| Re: Slow Query [message #4314 is a reply to message #4308] |
Tue, 26 November 2002 11:42 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Why write a bunch of procedural code that will run slower and consume more resources (redo, undo)? A single statement is almost always preferable - especially in this case.
As far as the query, you would obviously want an index on actname(cnumber). I would recommend one of the following (depending on the percentage of rows that are being updated):
-- if a subset (say, less than 90%) of the rows will be updated
update act
set full_name = (select full_name
from actname
where actname.cnumber = act.cnum)
where exists (select null
from actname
where actname.cnumber = act.cnum);
-- if most of the rows will be updated
update act
set full_name = nvl((select full_name
from actname
where actname.cnumber = act.cnum), full_name);
Your original query would probably work best if the percentage of updated rows was fairly low. So, the best approach really depends on how many rows will be updated.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
