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 Optimizer goal: CHOOSE Parsing user id: 23 (ADVDB) (recursive depth: 1) ******************************************************************************** OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 0 0.00 0.00 0 0 0 0 Execute 0 0.00 0.00 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 0 0.00 0.00 0 0 0 0 Misses in library cache during parse: 0 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 0 0.00 0.00 0 0 0 0 Execute 110420 645.36 1846.90 0 441240 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 110420 645.36 1846.90 0 441240 0 0 Misses in library cache during parse: 0 2 user SQL statements in session. 0 internal SQL statements in session. 2 SQL statements in session. 1 statement EXPLAINed in this session. ******************************************************************************** Trace file: ././ent9i_ora_25786.trc Trace file compatibility: 9.00.01 Sort options: prsela exeela fchela 1 session in tracefile. 2 user SQL statements in trace file. 0 internal SQL statements in trace file. 2 SQL statements in trace file. 2 unique SQL statements in trace file. 1 SQL statements EXPLAINed using schema: ADVDB.prof$plan_table Default table was used. Table was created. Table was dropped. 1875534 lines in trace file. alter session set nls_date_format ='DD-MON-YYYY HH24:MI:SS'; select adno,pubno,vno,vnoflag,mdate from advdb.pub_14 where adno=&adno / select adno,pubno,vno,vnoflag,pub_sysdate from advdb.pub where adno=&adno / Enter value for adno: 4335349 old 2: where adno=&adno new 2: where adno=4335349 ADNO PUBNO VNO V MDATE ---------- ---------- ---------- - -------------------- 4335349 1 1 N 17-JUL-2002 14:36:00 4335349 1 2 N 17-JUL-2002 14:39:00 4335349 1 3 Y 17-JUL-2002 14:39:00 4335349 2 1 N 17-JUL-2002 14:36:00 4335349 2 2 N 17-JUL-2002 14:39:00 4335349 2 3 Y 17-JUL-2002 14:39:00 4335349 3 1 N 17-JUL-2002 14:36:00 4335349 3 2 N 17-JUL-2002 14:39:00 4335349 3 3 Y 17-JUL-2002 14:39:00 9 rows selected. Enter value for adno: 4335349 old 2: where adno=&adno new 2: where adno=4335349 ADNO PUBNO VNO V PUB_SYSDATE ---------- ---------- ---------- - -------------------- 4335349 1 1 N 23-DEC-2003 13:10:01 4335349 1 2 N 23-DEC-2003 13:10:01 4335349 1 3 Y 23-DEC-2003 13:10:01 4335349 2 1 N 23-DEC-2003 13:10:01 4335349 2 2 N 23-DEC-2003 13:10:01 4335349 2 3 Y 23-DEC-2003 13:10:01 4335349 3 1 N 23-DEC-2003 13:10:01 4335349 3 2 N 23-DEC-2003 13:10:01 4335349 3 3 Y 23-DEC-2003 13:10:01 9 rows selected. __________________________________ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/