Re: INSERT into HUGE table

The percentage of duplicate rows is around 0.01%. That's why it's a problem the users don't want to pay so much for in reponse time. ('but that ALMOST never happens!').

I am already sourcing the data from a staging-like table.

Considering the advice I got from several people, ill think I will go for deleting the duplicate rows from the source. Staging and target are equal tables, so with some optimazation, I hope this will run sufficiently fast.

Thanks for your advice!

Jonathan Lewis wrote:

> 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.
> >
> >
Received on Fri Aug 11 2000 - 00:00:00 CDT