How can I run a bulk update in Oracle 10g (code provided)

calibreinc used Ask the Experts™
I'm running an update on a partitioned table in Oracle 10g and though it's making use of the required indeces and partitions, however the query analyzer comes back stating it's going to take days to complete.  Each partition contains approximately 20 million rows, and I'm scratching my head.  I did note a couple of articles posted on bulk updates, but I'm not sure if this would work as I would think it should.  One would think that with logging off the table should update in a much shorter period of time.  Any insight and assistance would be appreciated.
update raw_armylog a
set (a.pr_niin,a.pr_fsc) =
   (select nvl(b.pr_niin,nvl(c.pr_niin,a.niin)),nvl(b.pr_fsc,nvl(c.pr_fsc,nvl(a.fsc,''))) from mdr423 b,mdr462 c
      where  (b.niin (+) = a.niin and b.month (+) = a.month and b.year(+) = a.year) and 
      (c.niin (+) = a.niin and c.month (+) = a.month and c.year(+) = a.year)) WHERE a.fy=2004;

Open in new window

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2009
1) You can run updates on each partition in parallel from different sessions
2) You can rewrite your correlated subquery as a updateable view for much better performance (see sample below)
3) Disabling logging will require you to do a full backup after the operation or you won't be able to recover those changes.

-- BULK UPDATE with inline view. Table must be key preserved (primary key)
-- Also must alias the columns in the inline view, as the outer SET statement
-- cannot make use of the inner table aliases.
UPDATE (SELECT a.col1 as a_col1, b.col1 as b_col1
        FROM a INNER JOIN b
        ON ( =
SET a_col1 = b_col1

Open in new window

what`s you server configuration?


and also show explain plan output for this query


parallel_max_servers = 40
pga_aggregate_target = 203423744
sga_max_size = 612368384

I'll get the explain plan after I try the first suggestion passed to me.  I'm running it now against my smaller database to see how it performs.

so show explain plan for your smaller database

is the table A partitioned by fy or has any indexes on that field?


Thanks to all, the first response actually works better than I expected.  

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial