I have a need to perform a complete refresh of production table (~ 35M rows) on a daily basis using data that is received from an external source. The received data represents the most up to update copy of the entire table (not just incremental changes as gotten from this external system). These changes can be inserts, updates, and deletes. I have no way of telling the precentage of data that gets changed or note.
My options are ... 1) Using merge statement:- I can not tell before hand what columns get updated. I assume I can update all columns when matched. This may end up updating entire table. no good. - Will not sync deleted rows from the source so they are deleted on the destination. The delete option of this statement does not apply.
2) Using plsql with bulk select and bulk dml. This probably implies touching every row in the table and applying some logic to decide on its fate i.e. update, insert, or delete into the destination.
3) Resort into a procedure of table renames and the associated contraint/index operations. Potentially running into ddl locks in an oltp system.
4) Oracle Steams ?What other options are there that are efficient and least impacting on an oltp system?
Thank you. -- //www.freelists.org/webpage/oracle-l
