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

  • From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Mon, 26 Jan 2004 15:33:19 -0700

Shouldn't that be one subquery for 17,500,000 rows (which would be an 
index_only lookup which fails) and 2 subqueries for 500,000 rows (the 2nd 
of which would piggy-back on the just retrieved index entry).

I have just replaced several of these programmed loops (not pl/sql, even 
worse, client program) with this kind of single sql update statement. I 
don't have an 18M row table among them. The biggest is 4.5M rows of which 
practically all have a match in the other table and get updated. The update 
takes ~ 1:20 hours.

One could also try a hash_sj hint to see if that speeds things up.

At 01:48 PM 1/26/2004, you 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).
>Jonathan Lewis

Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation

Please see the official ORACLE-L FAQ: http://www.orafaq.com
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.

Other related posts: