Large Table Update/Merge problem

[Raghu]

I have a temprary table with 5 million rows (A)
which needs to be appended with 90 million row table (B).

60% of the rows of the 5mil rows already exist in the big table

i need to update/merge the table A data with table B
Oracle version is 8.1.7

Please advice which method is the fastest.
i need to do it <30 mins

[Frank]

Re: Large Table Update/Merge problem

Is the table B in another database? or on the same database? or in non
database format (flat file)? or non oracle database?

raghuraj_dasari @yahoo.com (Raghu) wrote in message news:<45d4e55e. 0408172303.e6aa dec@posting.goo gle.com>...

I have a temprary table with 5 million rows (A)
which needs to be appended with 90 million row table (B).
>
60% of the rows of the 5mil rows already exist in the big table
>
i need to update/merge the table A data with table B
Oracle version is 8.1.7
>
Please advice which method is the fastest.
i need to do it <30 mins

[Pratap]

Re: Large Table Update/Merge problem

Do the update first.

update
(
select a.col1 acol1, b.col1 bcol1
from a, b
where a.key = b.key
)
set acol1 = bcol1

make sure you have unique key on the "key" columns of both tables.

Then go for the insert -

insert into b select * from a where not exists
(
select null from b where a.key = b.key
)

You may need to set statistics on the temporary tables to get an efficient plan.
(Cannot gather statistics on temporary tables)


Pratap Deshmukh
Cognizant Technology Solutions, India

[Cristian Vanti]

Re: Large Table Update/Merge problem

raghuraj_dasari @yahoo.com (Raghu) wrote in message news:<45d4e55e. 0408172303.e6aa dec@posting.goo gle.com>...

I have a temprary table with 5 million rows (A)
which needs to be appended with 90 million row table (B).
>
60% of the rows of the 5mil rows already exist in the big table
>
i need to update/merge the table A data with table B
Oracle version is 8.1.7
>
Please advice which method is the fastest.
i need to do it <30 mins

Fastest method is using SQLLoader:
- export temp table in a flat file
- make sure a primary key exist on dest table
- allow SQLLoader accept 60% * 5mil = 3mil errors ( or adjust as you
prefer )
- load flat file via SQL Loader.

You can also write some PL/SQL to insert a row a time catching and
ignoring dup key exception. Commit every some thousand not to let
rollback seg grow too much

Insert 'where not exist' is terrible.

Bye
Cristian