I've a table with more than 10 billion records. Many NEW records (in quantum of 40-50 million records)are added regularly in a file as input part of batch process at night. The active table is majorly used for read operations throughout the day. To facilitate fast insert we keep active and inactive table. Everyday we first wipe out the data from inactive table, copy the existing data from active table to inactive table and insert new records. Lastly switch the inactive table to make it active.

I need to know how can I avoid duplicates records existing records to be copied from active table and in NEW records while copying. eg.

customer_num, help_number, issue_number issue_details, issue_closed_status

customer_num, help_number, issue_number, issue_details, issue_closed_status

The table doesn't have partitions and supports global index on customer_num, help_number, issue_number columns. We are using oracle 11g

  • This doesn't make any sense as a procedure, unless you're only selectively copying from the active to inactive before the load? – Philᵀᴹ Feb 11 '15 at 20:09
  • Yes I will be selectively copying from active to inactive based on issue_number and issue_closed_status. – pratikch Feb 11 '15 at 20:34

I am not an Oracle expert, but I can answer from DB2 perspective, you may look for similar features/commands in Oracle. In DB2, you can achieve this by:

  1. Using Import command with INSERT_UPDATE option.
  2. You also have a choice to use MERGE statement to merge data between two tables.

Hope it helps you.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.