[oracle-l] Re: PL/Sql Update Table runs 38 hrs (so far)

  • From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Mon, 26 Jan 2004 13:25:00 -0700

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
>I have been trying for several days to update a field
>in one table (pub) from a field in another table
>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
>   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;
>   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;
>   CLOSE pub14_cur;
>   DBMS_OUTPUT.PUT_LINE (v_insert||' records were

Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation

Other related posts: