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

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 (AT) yahoo (DOT) com (Raghu) wrote in message news:<45d4e55e.0408172303.e6aadec (AT) posting (DOT) google.com>...

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

raghuraj_dasari (AT) yahoo (DOT) com (Raghu) wrote in message news:<45d4e55e.0408172303.e6aadec (AT) posting (DOT) google.com>...
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