Re: Ignore Duplicates on Insert???

I would first create the table, then remove duplicates and then create the index for fastest possible throughput. I would also suggest that you have your table and indexes in different tablespaces using different physical disk to reduce I/O time (head seek time) to the mininum possible.

Since your table seems to be big, I would suggest you create the new table with the 'NOLOGGING'/'UNRECOVERABLE' option. This will prevent all data being logged in the REDO log buffers.

If that still does not solve your problem, dump the original table in text and tab delimited format and import it into the DB into the new table using SQL Loader Direct Path (again the NOLOGGING option should be set on the table for fastest possible times).

Thanks,

-Mohsin

stuco_at_mailcity.com wrote:

> I have a 3 million-row table that has a bunch of duplicates and no Primary
> Key. I need to insert all of the rows from that table into another table of
> identical structure with a Primary Key.
>
> Here's the PL/SQL I am using:
>
> BEGIN
> INSERT INTO table1
> SELECT * FROM table2;
> EXCEPTION
> WHEN DUP_VAL_ON_INDEX THEN NULL;
> END;
>
> My goal is to insert all unique rows as defined by the Primary Key and ignore
> duplicates, so that the insert continues when it hits a constraint violation.
>
> I have tried to delete duplicates, but the key is four columns and a mere
> select takes hours.
>
> Thanks to all in advance!
>
> Stuart L. Cowen
> Paladin Consulting
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Mon Dec 28 1998 - 23:52:46 CST