I am looking for the most efficient way to solve this situation:
We are an Oracle shop. I need to load data from one table to another. My source table, which contains 30 million rows, has a person identifier that is supposed to be unique, but it is not enforced so there are cases where it is not - about .1% of the time. There is logic I can use to resolve the non-unique cases. I want my target table to "enforce" the uniqueness of the identifier - so I have defined this identifier as my primary key.
(Note that the data that is loaded isn't a straight source table -> target table; there are a few look-up tables that are used too)
My goal: load this table as quickly as possible. It's a lot of rows, so efficiency is key.
I have thought of a number of ways to address this, but I'm not sure what would be the most efficient. My ideas include:
- Use MERGE (concerned about performance compared to an INSERT with an APPEND hint.)
- Change the SELECT statement of the load to include the logic to address the the non-unique cases
- Use error trapping to write the non-unique information to a separate table (where I would use the logic)
- Drop the primary key, load the data, fix the issues, add the key back
I am sure there are other methods that I haven't listed.
So - what's the fastest way to accomplish this task?
log errors into
together with theinsert
statement. Don't know if that is compatible with theAPPEND
hint though: docs.oracle.com/cd/E11882_01/server.112/e26088/… – a_horse_with_no_name Apr 23 at 13:45log errors into
option, but thus far it seems really slow. Duplicates are the same person. – JHFB Apr 23 at 19:50