[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).
>
>
>
>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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
-------------------------------------------------------------
Other related posts:
