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