I'd like to thank everyone for their suggestions and
insight.
I'm currently conferring with the developer about how
to proceed. Since he knows the data, I'll let him
decide what he thinks is best.
Again, thank you so much for taking the time to look
at the query and offer suggestions.
You guys are great!
Barb
- Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
wrote:
>
> 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_at_perceptron.com>
> To: <oracle-l_at_freelists.org>
> 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_at_perceptron.com
>
>
>
>
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 - 20:20:30 CST