Your update will update all the 18,000,000 rows in the advdb.pub - when the pl/sql loop may update only a small number of them. Your query will set a value to null in advdb.pub if there is no matching row in the smaller table, unless you add an existence test which repeats the updating subquery. Your query will crash with 'subquery returns more than one row) if there are any rows in pub14 which are duplicate entries on the indexed columns (we haven't been told that the indexes created were unique indexes). The pl/sql will update the related advdb.pub rows multiple times in this case, but it will not crash. If we had a guarantee of uniqueness on (pub14.adno, pub14.pubno, pub14.vno), you could take your idea one step further, though, and do the whole update using an updatable join view, something like: update ( select /*+ ordered use_hash */ pub17.pub_sysdate p17_pub_sysdate, pub14.mdate from advdb.pub_14 pub14, advdb.pub pub17 where pub17.adno = pub14.adno and pub17.pubno = pub14.pubno and pub17.vno = pub14.vno and pub17.pub_sysdate <> pub14.mdate ) set p17_pub_sysdate = p14_mdate 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:00 PM Subject: [oracle-l] Re: PL/Sql Update Table runs 38 hrs (so far) Still, didn't see/hear why couldn't it be done in a single SQL (as I suggested). Igor Neyman, OCP DBA ineyman@xxxxxxxxxxxxxx -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Barbara Baker Sent: Monday, January 26, 2004 2:37 PM To: oracle-l@xxxxxxxxxxxxx Subject: [oracle-l] Re: PL/Sql Update Table runs 38 hrs (so far) Doh! Apologies to the list. Thanks so much to Mike and Raj -- virtual beers for you both! (Unless either of you will be at RMOUG, in which case the beer can be of the non-virtual nature.) Thanks so much for looking at this Barb