[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
them.

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,
            pub14.mdate
    from
        advdb.pub_14             pub14,
        advdb.pub                 pub17
    where            
            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



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: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
suggested).

Igor Neyman, OCP DBA
ineyman@xxxxxxxxxxxxxx



-----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)

Doh!

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

Barb


Other related posts: