[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 20:26:40 -0000

Your update will update all the 18,000,000
rows in the advdb.pub - when the pl/sql
loop may update only a small number of

Your query will set a value to null in advdb.pub
if there is no matching row in the smaller table,
unless you add an existence test which repeats
the updating subquery.

Your query will crash with 'subquery returns more
than one row) if there are any rows in pub14 which
are duplicate entries on the indexed columns (we
haven't been told that the indexes created were unique 
indexes).  The pl/sql will update the related advdb.pub 
rows multiple times in this case, but it will not crash.

If we had a guarantee of uniqueness on (pub14.adno,
pub14.pubno, pub14.vno), you could take your idea
one step further, though, and do the whole update
using an updatable join view, something like:

update (
    select    /*+ ordered use_hash */
            pub17.pub_sysdate p17_pub_sysdate,
        advdb.pub_14             pub14,
        advdb.pub                 pub17
            pub17.adno     = pub14.adno
    and    pub17.pubno    = pub14.pubno
    and    pub17.vno      = pub14.vno
    and    pub17.pub_sysdate <> pub14.mdate
set p17_pub_sysdate = p14_mdate


Jonathan Lewis

  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:

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html

The Co-operative Oracle Users' FAQ

----- Original Message ----- 
From: "Igor Neyman" <ineyman@xxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Monday, January 26, 2004 8:00 PM
Subject: [oracle-l] Re: PL/Sql Update Table runs 38 hrs (so far)

Still, didn't see/hear why couldn't it be done in a single SQL (as I

Igor Neyman, OCP DBA

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Barbara Baker
Sent: Monday, January 26, 2004 2:37 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: [oracle-l] Re: PL/Sql Update Table runs 38 hrs (so far)


Apologies to the list.
Thanks so much to Mike and Raj -- virtual beers for
you both!

(Unless either of you will be at RMOUG, in which case
the beer can be of the non-virtual nature.)

Thanks so much for looking at this


Other related posts: