Hey! It's just like in your book, pg 230, in that
section called in-line updatable views. Doh.
Except that you substitute the 'use_hash' hint for
use_nl(bt) hint, because the book has one big and one
small table and here we've got two big (enough)
tables. I've updated my book with appropriate notes,
alternatives, etc. from this example.
Thanks Jonathon.
Regards,
Mike Thomas
- Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
wrote:
>
> 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
>
>
Do you Yahoo!?
Yahoo! SiteBuilder - Free web site building tool. Try it!
http://webhosting.yahoo.com/ps/sb/
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
To unsubscribe send email to: oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
Received on Mon Jan 26 2004 - 19:25:51 CST
