0

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.

transaction_data_active
customer_num, help_number, issue_number issue_details, issue_closed_status

transaction_data_inactive
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

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

1 Answer 1

Reset to default
1

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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