| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> [oracle-l] Re: PL/Sql Update Table runs 38 hrs (so far)
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
 |
 |