patternMinor
Limit redo for materialized view complete refresh or manual equivalent
Viewed 0 times
equivalentlimitmaterializedmanualrefreshviewcompleteforredo
Problem
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.
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.
Solution
Good question. I "solved" this problem for my situation a while back by making the MV's and any indexes on them NOLOGGING. There was no point to it my situation - I was doing a full refresh of the view anyway, why would I need redo?
Context
StackExchange Database Administrators Q#3251, answer score: 6
Revisions (0)
No revisions yet.