update advdb.pub pub17 SET pub17.pub_sysdate = (select pub14.mdate from advdb.pub_14 pub14 WHERE pub17.adno = pub14.adno AND pub17.pubno = pub14.pubno AND pub17.vno = pub14.vno ) where exists (select null from advdb.pub_14 p WHERE pub17.adno = p.adno AND pub17.pubno = p.pubno AND pub17.vno = p.vno ); I must be missing something as it seems too simple. I am assuming that (adno,pubno,vno) provide a unique index into pub_14, otherwise you need to somehow clarify which of the multiple mdate-s you want. At 11:46 AM 1/26/2004, you wrote: >Hi, list. >Solaris 9 >Oracle 9.2.0.4 > >I have been trying for several days to update a field >in one table (pub) from a field in another table >(pub_14) >The table I'm updating FROM (pub_14) has about 500,000 >rows in it. >The table I'm updating (pub) has about 18,000,000 rows >in it. > >I'm on about my 5th attempt. The current version has >been running for 38 hours. So far I believe I've >managed to update about 500 records. > >The tables originally were identical in structure, but >1 came from another database. To eliminate link >issues, I created a new table (pub_14) with just the 5 >fields I need. > >Both tables have an index on these 3 columns (adno, >pubno, vno). I've analyzed both tables. The cost is >lower with the hints I've provided, but I don't really >think it makes any difference. > >I turned on 10046 level 12 for the current process (38 >hour one). In just a few minutes of tracing, I see >bunches of executes, but no updates. > >Any ideas? >Here's my update pl/sql, tkprof from the 10046 trace, >and a sample of one of the sets of adno's that need to >be updated. > >pacer:ent9i> more update_pub_from_mdate.sql > >set serveroutput on size 1000000 > >DECLARE > CURSOR pub14_cur IS > SELECT pub14.adno, > pub14.pubno, > pub14.vno, > pub14.vnoflag, > pub14.mdate > FROM advdb.pub_14 pub14; > pub14_rec pub14_cur%ROWTYPE; > > v_insert NUMBER(9,0) := 0; > >BEGIN > OPEN pub14_cur; > FETCH pub14_cur INTO pub14_rec; > LOOP > EXIT WHEN pub14_cur%NOTFOUND; > UPDATE > /*+ index(pub17 I_PUB1) > use_hash (pub14 pub17) */ >advdb.pub pub17 > SET pub17.pub_sysdate = pub14_rec.mdate > 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; > > 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; > >/ Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com