Hi,
I have a table (let's call it table_A) that is sourced from multiple other tables, having around 300k records
now, I want to refresh this table on a daily basis with the updated information. Here are a few options that I was thinking off (probably there more and better) and wanted to get your opinion
option 1)
Step 1. create a table (one time)
Step 2. Delete all records that have been changed, since the last table refresh
Step 3. Delete all records that have been deleted from the other tables, since the last table refresh
Step 4. Add records (with new data) that has been changed, since the last table refresh
Step 5. Add new records that have been added, since the last table refresh
Option 2)
Step 1. create a table (one time)
Step 2. Delete all records that have been deleted from the other tables, since the last table refresh
Step 3. Add new records that have been added, since the last table refresh
Step 4. update records (with new data) that has been changed, since the last table refresh
The difference between options 1 and 2 is that in option 1 I am Deleting a record that has been changed since the last refresh and Inserting that record. Vs, with option 2, I am using the oracle update function.
The assumption is that with option 2, an update function will work efficiently as it will not require indexing for the records that are updated. vs. option one will require indexing as It is deleting and adding new records for all the updates.
Please advise
Thanks