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 --- Mike Spalinger <Michael.Spalinger@xxxxxxx> wrote: > Barbara, > > Should your FETCH be inside your LOOP? > > Mike > > Barbara Baker 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; > > > > / > > > > TKPROF: Release 9.2.0.4.0 - Production on Mon Jan > 26 > > 11:03:01 2004 > > > > Copyright (c) 1982, 2002, Oracle Corporation. All > > rights reserved. > > > > Trace file: ././ent9i_ora_25786.trc > > Sort options: prsela exeela fchela > > > ******************************************************************************** > > count = number of times OCI procedure was > executed > > cpu = cpu time in seconds executing > > elapsed = elapsed time in seconds executing > > disk = number of physical reads of buffers > from > > disk > > query = number of buffers gotten for consistent > > read > > current = number of buffers gotten in current > mode > > (usually for update) > > rows = number of rows processed by the fetch > or > > execute call > > > ******************************************************************************** > > > > UPDATE > > /*+ index(pub17 I_PUB1) > > use_hash (pub14 pub17) */ > > advdb.pub pub17 > > SET pub17.pub_sysdate = :b1 > > WHERE pub17.adno = :b4 > > AND pub17.pubno = :b3 > > AND pub17.vno = :b2 > > AND pub17.pub_sysdate <> :b1 > > > > call count cpu elapsed disk > > > query current rows > > ------- ------ -------- ---------- ---------- > > ---------- ---------- ---------- > > Parse 0 0.00 0.00 0 > > > 0 0 0 > > Execute 110310 645.28 1846.76 0 > > 441240 0 0 > > Fetch 0 0.00 0.00 0 > > > 0 0 0 > > ------- ------ -------- ---------- ---------- > > ---------- ---------- ---------- > > total 110310 645.28 1846.76 0 > > 441240 0 0 > > > > Misses in library cache during parse: 0 > > Optimizer goal: CHOOSE > > Parsing user id: 23 (ADVDB) (recursive depth: > 1) > > > > Rows Execution Plan > > ------- > > > --------------------------------------------------- > > 0 UPDATE STATEMENT GOAL: CHOOSE > > 0 UPDATE OF 'PUB' > > 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX > > ROWID) OF 'PUB' > > 0 INDEX GOAL: ANALYZED (UNIQUE SCAN) > OF > > 'I_PUB1' (UNIQUE) > > > > > ******************************************************************************** > > > > COMMIT > > > > > > call count cpu elapsed disk > > > query current rows > > ------- ------ -------- ---------- ---------- > > ---------- ---------- ---------- > > Parse 0 0.00 0.00 0 > > > 0 0 0 > > Execute 110 0.08 0.13 0 > > > 0 0 0 > > Fetch 0 0.00 0.00 0 > > > 0 0 0 > > ------- ------ -------- ---------- ---------- > > ---------- ---------- ---------- > > total 110 0.08 0.13 0 > > > 0 0 0 > > > > Misses in library cache during parse: 0 > === message truncated === __________________________________ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/