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

  • From: "Ron Thomas" <rthomas@xxxxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Mon, 26 Jan 2004 14:18:06 -0700

Except this would violate one of my pet peeves...

Thou shalt not commit inside a cursor loop

Ron Thomas
Hypercom, Inc
rthomas@xxxxxxxxxxxx
"The box said I needed to have windows 98 or better...So I installed linux."


                                                                                
                                                                       
                      ktoepke@rlcarriers                                        
                                                                       
                      .com                      To:       
oracle-l@xxxxxxxxxxxxx                                                          
             
                      Sent by:                  cc:                             
                                                                       
                      oracle-l-bounce@fr        Subject:  [oracle-l] Re: PL/Sql 
Update Table runs 38 hrs (so far)                                      
                      eelists.org                                               
                                                                       
                                                                                
                                                                       
                                                                                
                                                                       
                      01/26/2004 02:08                                          
                                                                       
                      PM                                                        
                                                                       
                      Please respond to                                         
                                                                       
                      oracle-l                                                  
                                                                       
                                                                                
                                                                       
                                                                                
                                                                       




Why not use a cursor for loop? Makes the code easy to read as well as
reduces errors (such as having the fetch in the wrong place!)

Kevin

DECLARE
  CURSOR pub14_cur IS
    SELECT pub14.mdate
      FROM advdb.pub_14 pub14, advdb.pub pub_17
      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
      FOR UPDATE OF mdate;
  v_insert NUMBER(9,0) := 0;

BEGIN
  FOR pub14_rec IN pub14_cur LOOP
    UPDATE advdb.pub pub17
       SET pub17.pub_sysdate = pub14_rec.mdate
     WHERE CURRENT OF pub14_cur;

    v_insert := v_insert + 1;

    IF MOD(v_insert,1000) = 0 THEN
        COMMIT;
    END IF;
  END LOOP;
  COMMIT;
  DBMS_OUTPUT.PUT_LINE (v_insert||' records were inserted.');
END;

-----Original Message-----
From: paul bennett [mailto:pbennett@xxxxxxxxxxxx]
Sent: Monday, January 26, 2004 4:04 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: [oracle-l] Re: PL/Sql Update Table runs 38 hrs (so far)


Here is some untested code that might address some of the issues:
DECLARE
  CURSOR pub14_cur IS
    SELECT pub17.ROWID row_id,
           pub14.mdate
      FROM advdb.pub_14 pub14, advdb.pub pub_17
  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;
  pub14_rec pub14_cur%ROWTYPE;
  v_insert NUMBER(9,0) := 0;

BEGIN

  OPEN pub14_cur;

  LOOP

    FETCH pub14_cur INTO pub14_rec;
    EXIT WHEN pub14_cur%NOTFOUND;

    UPDATE advdb.pub pub17
       SET pub17.pub_sysdate = pub14_rec.mdate
     WHERE pub17.ROWID       = pub14_rec.row_id;

    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;

Paul Bennett


>>> jonathan@xxxxxxxxxxxxxxxxxx 01/26/04 02:41PM >>>

That will work, given Wolfgang's assumption
about uniqueness. But as it stands, Oracle will
have to execute two subqueries for every row
in the 18,000,000 row table (I'm not sure that
any of the optimizer versions is currently smart
enough to convert his query into a hash join
with subquery update - but don't take my word
for that, I haven't tested it).

The pl/sql loop will make a maximum of 500,000
probes into the 18,000,000 row table to update.

(I think we are also both assuming that all three
of the join columns are not null, but the pl/sql
may behave contrary to the OP's expectations
if that were the case).



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: "Igor Neyman" <ineyman@xxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Monday, January 26, 2004 8:33 PM
Subject: [oracle-l] Re: PL/Sql Update Table runs 38 hrs (so far)


Agreed.
What about modified code Wolfgang suggested?

Igor Neyman, OCP DBA
ineyman@xxxxxxxxxxxxxx








Other related posts: