Re: how to rebuild index after direct path load?

<hkpang_at_my-deja.com> wrote in message news:81hbld$6nu$1_at_nnrp1.deja.com...
> Hi,
>
> I need to upload a huge dataset into an existing table. If the dataset
> contains duplicate keys, I want to the delete the old ones already in
> the table.
>
> My current approach is using sqlldr direct path. Since the data
> contains duplicate keys, the primary key index is violated and put in
> direct load state afterward.
>
> Now my problem is how to rebuild the index *and* eliminate the
> duplicates?
>
> 1) use a delete command to eliminate the duplicates. this kind of works
> but the performance is unacceptable. i potentially have millions to
> delete.
>
> 2) drop the index before upload, reenable the index with exceptions
> going to an exception table. the probelm is that both the old rows and
> the new rows are in the table, there is no way I can figure out which
> one to eliminate.
>
> Any suggestions???
>
> Many thanks for making a happy thanksgiving possible. :-)

Okay, I have two suggestions. The simplest is to have your unique/primary key constraint enabled when you do your SQL*Load (I think for this you have to use the conventional path). Here, all exceptions won't be loaded by default, and their rownums will be written to a bad file. Oh, you have to make sure that your number of exceptions is defined as very large in your SQL*Loader control file.

Secondly, if you use method (2) you can determine the rowids of all columns that cause an exception from the EXCEPTIONS table. It would be a very efficient DELETE to delete from your table where rowid = ... I'd opt for the latter, since I would think it was faster, and also you have more control over what is/isn't deleted.

HTH Received on Thu Nov 25 1999 - 07:57:18 CST