Re: Most efficient large update?

Try

update
(

select	t.to_data
	, f.from_data
from	from_table f
	, to_table t
where	t.key = f.key
) x
set	x.to_data = x.from_data

Necessary precondition is that you have a unique index on from_table (key).

You can parallelize this statement by partitioning to_table, allowing parallel dml for the executing user and her/his session.

Advantage: you will only touch those rows of to_table that have a pendant in from_table. All others will not be read.

Martin

broom wrote:
>
> I'm trying to figure out the most efficient
> method of populating a field from one table
> to another. I've got to update about 100 million
> records, drawing from about 20 million.
>
> From_table:
>
> key - char(10)
> from_data - char(10)
>
> To_table:
> key - char(10)
> about 100 fields.
> to_data - char(10)
> 50 more fields.
>
> Goal is to populate the to_data
> with the from_data, by 'key'.
>
> I figure my best case would be a
> pl/sql loop, select bulk gathering
> 100,000 records at a time. For each
> of these loops, doing a bulk update
> of the target table, committing in
> batches of 100,000 records.
>
> I'm blathering based on wandering though
> some Oracle books, being a complete newbie
> to pl/sql.
>
> Would anybody be able to point me to an example
> of this type code?
>
> Thanks.
>
> If possible, please email and I'll summarize.
>
> Barry
>
> broom-nospam_at_voicenet.com
Received on Wed May 30 2001 - 00:50:51 CDT