[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:41:09 -0000

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


Jonathan Lewis

  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:

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html

The Co-operative Oracle Users' FAQ

----- Original Message ----- 
From: "Igor Neyman" <ineyman@xxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Monday, January 26, 2004 8:33 PM
Subject: [oracle-l] Re: PL/Sql Update Table runs 38 hrs (so far)

What about modified code Wolfgang suggested?

Igor Neyman, OCP DBA

Other related posts: