| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> [oracle-l] Re: PL/Sql Update Table runs 38 hrs (so far)
Mike, it was very gracious of you to put it that way.
;-)
PB
--- Mike Spalinger <Michael.Spalinger_at_Sun.COM> 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
> >
>
 |
 |