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

Thanks.

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 Jonathan Lewis Sent: Monday, January 26, 2004 3:41 PM
To: oracle-l_at_freelists.org
Subject: [oracle-l] Re: PL/Sql Update Table runs 38 hrs (so far)

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 Received on Mon Jan 26 2004 - 14:48:06 CST