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

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 26 Jan 2004 20:26:40 -0000
Message-ID: <006501c3e44a$bbed74c0$6702a8c0@Primary>

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_at_perceptron.com> To: <oracle-l_at_freelists.org> 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_at_perceptron.com

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Barbara Baker Sent: Monday, January 26, 2004 2:37 PM
To: oracle-l_at_freelists.org
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 Received on Mon Jan 26 2004 - 14:26:40 CST