Re-summarise versus fast Mview refresh - part 2

The other day I talked about on commit refresh of m-views; of the other options for refresh, on demand is probably most useful for data warehouse users. Mviews can also be refreshed at fixed intervals, but this has the possible pitfall of the refresh happening before the data is loaded!

There are two fast refresh mechanisms: partition change tracking, which is probably more relevant to summarisation after fact data load; and materialized view log based refresh. It is this second form of refresh that is used in the update type of maintenance we encounter in re-summarisation.

As the name implies we need to maintain logs on the source tables for our aggregate materialized view to capture the DML changes to the underlying tables. These logs capture all kinds of DML on the source table and not just the changes we are concerned with in re-summarisation. Of course there is an overhead to logging DML, it slows the transactions on the source tables - that said it does capture INSERT /*APPEND */ efficiently but on the other hand this type of transaction is not common for dimensional data tables. It is not necessary to log changes for every column in the dimension table, just the ones that are subject to change. We can only specify one materialized view log per source table so we must make sure we filter on all of the relevant columns for all of the m-views that use the table. To get fast refresh to work in more cases the log should really be specified to use the ROWID, SEQUENCE and INCLUDING NEW VALUES clauses. But that is not the end of it we also need to include some extra bits in our materialized view. If we have a SUM() on column 'M' in our m-view we also need to include a count(M) - for each SUM column we need a matching count on the same column, we also should index the M-view with a composite index across all of the key values - Oracle automatically builds this function based index (unless you tell it not to) when you create the materialized view. Without this index Oracle would struggle to find the rows to update quickly.

So we get to the basic question. "Is rebuilding the summary (or complete refreshing the m-view) quicker than running a fast refresh to update the changed summaries?" This of course comes down to volume of change, there is a point where time taken to do N updates to a summary takes longer than the time allowed in the batch or even the time taken to:

truncate summary disable bitmap indexes

INSERT /APPEND/

re-index