Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
You can use log errors into together with the insert statement. Don't know if that is compatible with the APPEND hint though: docs.oracle.com/cd/E11882_01/server.112/e26088/… –  a_horse_with_no_name Apr 23 '13 at 13:45
    
if a duplicate row is a different person - then a merge will only allow you to update - not what you are looking for. if duplicates are new data then a merge is what you are looking for. –  haki Apr 23 '13 at 15:20
    
I am exploring the log errors into option, but thus far it seems really slow. Duplicates are the same person. –  JHFB Apr 23 '13 at 19:50
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.