[oracle-l] Re: PL/Sql Update Table runs 38 hrs (so far)
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 26 Jan 2004 19:35:01 -0000
The pl/sql you've sent has become almost
unreadable in transit, but it seems to me that
it would help if you put the LOOP above the
FETCH.
Your code looks as if it does one FETCH
from the cursor, then goes into an infinite
loop doing the same no-row-update update
committing every thousand attempts.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The educated person is not the person
who can answer the questions, but the
person who can question the answers -- T. Schick Jr
Next public appearances:
Jan 29th 2004 UKOUG Unix SIG - v$ and x$
March 2004 Hotsos Symposium - The Burden of Proof
March 2004 Charlotte NC OUG - CBO Tutorial
April 2004 Iceland
One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___February
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
----- Original Message -----
From: "Barbara Baker" <barbarabbaker@xxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Monday, January 26, 2004 6:46 PM
Subject: [oracle-l] PL/Sql Update Table runs 38 hrs (so far)
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;
/
Other related posts:
