A materialized view(MV) log can be used to allow a MV to do a fast refresh which only modifies the data that has changed. However, various conditions prevent the MV from using the log and therefore require a complete refresh. Oracle implemented an atomic complete refresh as a delete and insert of every record. It does this even if there are ultimately no changes to the data.
Is there a way to make this replication intelligent with regard to redo generation? A MERGE followed by a DELETE requires querying the source twice. Would it be worth it to bulk collect the data to do a BULK MERGE and DELETE? Is there a better way?
Update:
I explored using a global temporary table as a staging area. Although they use less than half the redo, they still use to much.
Best Answer
This is just intended to demonstrate redo usage of various
insert
operations rather than answer the whole question. Results on my 10g instance are not 100% deterministic, but the broad picture remained the same each time I ran through.For the heap tables, I do not know why the
insert /*+ append */
generated more redo.testbed:
test:
result: