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

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Mon, 26 Jan 2004 13:25:00 -0700
Message-Id: <6.0.0.22.2.20040126131436.02c4ddf0@pop.centrexcc.com>

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 Received on Mon Jan 26 2004 - 14:25:00 CST