Re: INSERT into HUGE table

It depends to a degree on your requirements for control methods, the percentage of rows that will introduce duplicates and how you are sourcing the data.

One option for a largely SQL solution is:

    load staging table
    delete from staging where exists duplicate in main table     insert into main table select from staging table

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Roger Palmen wrote in message <39927C92.9F93ADE1_at_alewijnse.nl>...


>Hi all,

>

>I'm trying to find the most efficient way to insert large amounts of

>records (lets say 100K a day) into a single huge table (100M records).

>However, the problem is that the set of records to insert contains

>records already in the target table. These duplicate records can either

>be overwritten or neglected.

>

>A simple but effective INSERT statement fails if only one record doesn't

>pass the PK-constraint. What is the most efficient way to do this?

>

>- Export / SQL-loader?

>- Delete rows from source?

>- Use PL/SQL and cursor to insert one record at a time?

>- Outer join with target?

>- etc?

>

>Using Oracle 8.1.6 on NT.

>

>Thanks for advice.

>Please reply to newsgroup, or pe1pvy_at_amsat.org NOT the sender.

>

>