calibreinc
asked on
How can I run a bulk update in Oracle 10g (code provided)
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;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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?
is the table A partitioned by fy or has any indexes on that field?
ASKER
Thanks to all, the first response actually works better than I expected.
parallel_max_servers
sga_max_size
pga_aggregate_target?
and also show explain plan output for this query