Have you ever wondered why it takes a century to completely refresh your materialized view? I did, so I did some testing.
Recently I was asked to support a customer whose database was extremely slow. As it turned out, some indexes had been created on a materialized view and that view was being refreshed. Soon I found that a large ‘delete from’ job was running, which turned out to be part of the complete refresh.
Materialized views can be refreshed in two ways: fast or complete. A fast refresh requires having a materialized view log on the source tables that keeps track of all changes since the last refresh, so any new refresh only has changed (updated, new, deleted) data applied to the MV.
A complete refresh does what it says: it completely refreshes all data in the MV. No materialized view logs are needed. And it takes a little longer.
A little? Hold your horses. MV’s are read consistent like any other table as well. So a refresh consists of a consistent delete and consistent insert. Meaning? All the time that the MV is refreshing, other sessions must be able to read the MV as it was before you started your refresh. So you have undo data, and redo/archivelog for all the delete. And not only for the MV itself but also for all indexes on that view.
I kept track of the timing and the number of archivelogs during some MV manipulation and the results are even more dramatic than I anticipated.
SQL> create materialized view mat_view1 as select * from IMO_SHIPMENT_ACTORS where code_role='HD'; Materialized view created. Elapsed: 00:00:47.17 SQL>
In the mean time, 15 archive log files were created, solely due to this transaction.
Next, I did a complete refresh.
SQL> exec DBMS_SNAPSHOT.REFRESH( '"OWNER1"."MAT_VIEW1"','C'); PL/SQL procedure successfully completed. Elapsed: 00:07:47.63 SQL>
A staggering 104 archivelogs were created, again solely due to this transaction! So there must have been a lot of redo generation too. And redolog writing is utterly important for database performance. If it can be limited somehow, do it.
Maybe I could just drop the MV and create it again?
SQL> @drop_and_create.sql Materialized view dropped. Elapsed: 00:00:01.85 Materialized view created. Elapsed: 00:00:54.08 SQL>
The number of logfiles this time was 17. When we create the MV with the NOLOGGING option there won’t even be any logfiles. Remember that, according to documentation, you should make a backup immediately afterward but in this specific case that is just silly.
Of course this is not always possible, some MV’s must always be available.
Another solution is using fast refresh. It’s quite useless to demonstrate here since the number of changes on the table determines how much work it is, as is the frequency of refreshing.
So there it is: try and avoid complete refreshes, use fast refresh of drop-and-create whenever possible.
A complete refresh with atomic_refresh => false uses a trunc instead of delete; so has also less redo.
Advantage in this approach is, you don’t have to recreate the indexes on the MV (just set unusable before and rebuild after the refresh).
Make two one-partition tables, do a truncate + refresh on one, and then exchange partition.
Near-zero data unavailability time.