Hi Kevin: I like the idea of using the cursor for a loop but I did not want to make too many changes to the original code. As written however I do not think your code will work. For one thing we need to update pub17.pub_sysdate and not pub14.mdate. Paul Bennett >>> ktoepke@xxxxxxxxxxxxxx 01/26/04 03:08PM >>> 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; v_insert NUMBER(9,0) := 0; 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@xxxxxxxxxxxx] Sent: Monday, January 26, 2004 4:04 PM To: oracle-l@xxxxxxxxxxxxx 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; v_insert NUMBER(9,0) := 0; 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@xxxxxxxxxxxxxxxxxx 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 ----- Original Message ----- From: "Igor Neyman" <ineyman@xxxxxxxxxxxxxx> To: <oracle-l@xxxxxxxxxxxxx> Sent: Monday, January 26, 2004 8:33 PM Subject: [oracle-l] Re: PL/Sql Update Table runs 38 hrs (so far) Agreed. What about modified code Wolfgang suggested? Igor Neyman, OCP DBA ineyman@xxxxxxxxxxxxxx