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

calibreinc
calibreinc used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2009
Commented:
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 (a.id = b.id)
       )
SET a_col1 = b_col1
;

Open in new window

Commented:
what`s you server configuration?

parallel_max_servers
sga_max_size
pga_aggregate_target?

and also show explain plan output for this query

Author

Commented:
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.

Commented:
so show explain plan for your smaller database

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

Author

Commented:
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