Re: Large Table Update/Merge problem

From: Cristian Vanti <cristian.vanti_at_tiscali.it>
Date: 19 Aug 2004 02:06:27 -0700
Message-ID: <a0844a7b.0408190106.6ac37624@posting.google.com>

raghuraj_dasari_at_yahoo.com (Raghu) wrote in message news:<45d4e55e.0408172303.e6aadec_at_posting.google.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 Received on Thu Aug 19 2004 - 04:06:27 CDT