| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> [oracle-l] Re: PL/Sql Update Table runs 38 hrs (so far)
Except this would violate one of my pet peeves...
Thou shalt not commit inside a cursor loop
Ron Thomas
Hypercom, Inc
rthomas_at_hypercom.com
"The box said I needed to have windows 98 or better...So I installed linux."
ktoepke_at_rlcarriers
.com To: oracle-l_at_freelists.org
Sent by: cc:
oracle-l-bounce_at_fr Subject: [oracle-l] Re: PL/Sql Update Table runs 38 hrs (so far)
eelists.org
01/26/2004 02:08
PM
Please respond to
oracle-l
Why not use a cursor for loop? Makes the code easy to read as well as reduces errors (such as having the fetch in the wrong place!)
Kevin
DECLARE
CURSOR pub14_cur IS
SELECT pub14.mdate
FROM advdb.pub_14 pub14, advdb.pub pub_17
WHERE pub17.adno = pub14_rec.adno
AND pub17.pubno = pub14_rec.pubno
AND pub17.vno = pub14_rec.vno
AND pub17.pub_sysdate <> pub14_rec.mdate
FOR UPDATE OF mdate;
BEGIN
FOR pub14_rec IN pub14_cur LOOP
UPDATE advdb.pub pub17
SET pub17.pub_sysdate = pub14_rec.mdate
WHERE CURRENT OF pub14_cur;
v_insert := v_insert + 1;
IF MOD(v_insert,1000) = 0 THEN
COMMIT;
END IF;
END LOOP;
COMMIT;
DBMS_OUTPUT.PUT_LINE (v_insert||' records were inserted.');
END;
-----Original Message-----
From: paul bennett [mailto:pbennett_at_good-sam.com]
Sent: Monday, January 26, 2004 4:04 PM
To: oracle-l_at_freelists.org
Subject: [oracle-l] Re: PL/Sql Update Table runs 38 hrs (so far)
Here is some untested code that might address some of the issues:
DECLARE
CURSOR pub14_cur IS
SELECT pub17.ROWID row_id,
pub14.mdate
FROM advdb.pub_14 pub14, advdb.pub pub_17
WHERE pub17.adno = pub14_rec.adno
AND pub17.pubno = pub14_rec.pubno
AND pub17.vno = pub14_rec.vno
AND pub17.pub_sysdate <> pub14_rec.mdate;
pub14_rec pub14_cur%ROWTYPE;
BEGIN OPEN pub14_cur;
LOOP
FETCH pub14_cur INTO pub14_rec;
EXIT WHEN pub14_cur%NOTFOUND;
UPDATE advdb.pub pub17
SET pub17.pub_sysdate = pub14_rec.mdate
WHERE pub17.ROWID = pub14_rec.row_id;
v_insert := v_insert + 1;
IF MOD(v_insert,1000) = 0
THEN COMMIT;
END IF;
END LOOP;
COMMIT;
CLOSE pub14_cur;
DBMS_OUTPUT.PUT_LINE (v_insert||' records were
inserted.');
END; Paul Bennett
>>> jonathan_at_jlcomp.demon.co.uk 01/26/04 02:41PM >>>
That will work, given Wolfgang's assumption about uniqueness. But as it stands, Oracle will have to execute two subqueries for every row in the 18,000,000 row table (I'm not sure that any of the optimizer versions is currently smart enough to convert his query into a hash join with subquery update - but don't take my word for that, I haven't tested it).
The pl/sql loop will make a maximum of 500,000 probes into the 18,000,000 row table to update.
(I think we are also both assuming that all three of the join columns are not null, but the pl/sql may behave contrary to the OP's expectations if that were the case).
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The educated person is not the person
who can answer the questions, but the
person who can question the answers -- T. Schick Jr
Next public appearances:
Jan 29th 2004 UKOUG Unix SIG - v$ and x$
March 2004 Hotsos Symposium - The Burden of Proof
March 2004 Charlotte NC OUG - CBO Tutorial
April 2004 Iceland
One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___February
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Agreed.
What about modified code Wolfgang suggested?
Igor Neyman, OCP DBA
ineyman_at_perceptron.com
Received on Mon Jan 26 2004 - 15:18:06 CST
 |
 |