The materialized view on WEST is created using the following code:
CREATE MATERIALIZED VIEW mv01 REFRESH FAST AS SELECT * FROM us01.t1@east1;Inspection of 10046 trace for a materialized view complete refresh of materialized view MV01 shows that the following statement is executed to remove the existing contents of the materialized view:
DELETE FROM mv01;In this case the materialized view contains a copy of all rows in the master table. There is a materialized view log on the master table and under normal circumstances I would expect to perform a fast refresh affecting a relatively small subset of rows. However, it is sometimes necessary to perform a complete refresh e.g. following a bulk load of data into the master table or after an application upgrade. If the materialized view on WEST includes a signifcant number of rows then it will probably be indexed. When the DELETE statement is executed, each row will be deleted individually followed by its associated index entries. This will generate significant undo and redo. In particular undo will be generated for the data in every column for each deleted row which may not be necessary. Following this investigation I now believe that, immediately prior to performing a complete refresh on a materialized view, it may be worth truncating the materialized view table. Obviously this will not be possible if application processes still need to access read-consistent data in the materialized view while it is being refreshed, but for most databases this is probably not relevant and significant reductions in refresh times may be possible.