Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> [oracle-l] Re: PL/Sql Update Table runs 38 hrs (so far)

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

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Mon, 26 Jan 2004 15:33:19 -0700
Message-Id: <6.0.0.22.2.20040126151232.02bc9ff8@pop.centrexcc.com>


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).
>
>
>
>Regards
>
>Jonathan Lewis
>http://www.jlcomp.demon.co.uk

Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com



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 - 16:33:19 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US