How Complete Refreshable Materialized View Works?

This article is inspired by a question repeatedly asked to me: Why do you use a Complete Materialized View and not a Table or just a View? When the data is not available during refresh. This is a great question and I asked this once to myself and came to know a lot about Materialized View. When I learned more on Materialized View, I started to love them so much and started using them more and write more about them. This has changed my life at work and improved the efficiency of my database and application using them.

There is no question on Fast Refreshable Materialized View regarding data availability during refresh. The data is available at all time even during refresh even though the data is not fresh. Now, we will ask the same question to a Complete Refresh Materialized view. What do you think? Is the data available during refresh? Complete refresh takes a while therefore it does not fall on Fast refresh category. The answer is Yes and No both for data availability while the complete refresh is happening. What is NO and Yes? Let's find that out now.

What is a complete Refreshable Materialized View? This type of Mview refreshes everything from Master tables. Master tables have no logs to keep track of DML, therefore, it refreshes everything from master tables to Mview. During refresh, the Mview data gets deleted/truncated and then inserted with data from master tables. I said it is either DELETED or TRUNCATED therefore if the data from Mview is DELETED and refreshed with a new set of data, the data is available during refresh. When truncated, the data is not available and needs to wait until the refresh is complete. What determines the refresh to use DELETE or TRUNCATE?

To answer the question above regarding DELETE or TRUNCATE, you will need to know how to refresh a complete materialized view?

SYNTAX:

DBMS_MVIEW.REFRESH (
   { list                 IN     VARCHAR2,
   | tab                  IN     DBMS_UTILITY.UNCL_ARRAY,}
   method                 IN     VARCHAR2       := NULL,
   rollback_seg           IN     VARCHAR2       := NULL,
   push_deferred_rpc      IN     BOOLEAN        := true,
   refresh_after_errors   IN     BOOLEAN        := false,
   purge_option           IN     BINARY_INTEGER := 1,
   parallelism            IN     BINARY_INTEGER := 0,
   heap_size              IN     BINARY_INTEGER := 0,
   atomic_refresh         IN     BOOLEAN        := true,
   nested                 IN     BOOLEAN        := false);

1. List|Tab: List of comma delimited Mviews.
2. Method: Refresh method where 'F' indicates Fast refresh, '?' indicates Force Refresh, 'C' indicates Complete refresh and 'P' refreshes by recomputing the rows in the Mview view affected by changed partitions.
3.  Atomic_Refresh: True refreshes Mview in a single transaction. False refreshes in a separate transaction.
4.  Parallelism: 0 specifies serial propagation, n>1 specifies parallel propagation.
5.  Rest: I don't care, you should too!

Example:  Atomic Transaction:

BEGIN
DBMS_MVIEW.REFRESH(LIST => 'MV_NAME', METHOD => 'C', ATOMIC_REFRESH => TRUE);
END;
/

The Materialized View is refreshed using a single transaction or atomic translation. The refresh occurs using delete from Mview and then Insert into Mview underlying table. Delete is a slow process that generates redo/undo logs. The positive side to this refresh method is that the data available during refresh. By default atomic_fresh parameter  is set to TRUE. Did this answer your question? Wait, we have more below to cover.
Example:  Non-Atomic Transaction:

BEGIN
DBMS_MVIEW.REFRESH(LIST => 'MV_NAME', METHOD => 'C', ATOMIC_REFRESH => FALSE);
END;
/

The Materialized View is refreshed using multiple transactions. The refresh happens using TRUNCATE from Mview and then Insert into Mview. TRUNCATE is faster compared to DELETE statements and generates minimum redo or undo logs. Since the refreshing process Truncates the MView, data are NOT available during refresh but the refresh time is much shorter compared to previous example with atomic transaction. This is the great way to improve the refresh time on a complete refresh able MView that is  taking longer to refresh.
Example: Atomic Transaction with Multiple MViews

BEGIN
DBMS_MVIEW.REFRESH(LIST => 'MV_NAME1, MV_NAME_2', METHOD => 'C', ATOMIC_REFRESH => TRUE);
END;
/

In this example, both the MViews  are refresh in a single transaction, meaning they will refresh the same time. Whenever there is an error occurred in one, both will be roll backed to previous state.
Example: Non-Atomic Transaction with Multiple MViews

BEGIN
DBMS_MVIEW.REFRESH(LIST => 'MV_NAME1, MV_NAME_2', METHOD => 'C', ATOMIC_REFRESH => FALSE);
END;
/

In this example, the refresh happens in two or more transactions. Which is like refreshing individual Mview. If one fails another can complete or vice versa.

Materialized Views are great Oracle objects that are easy to use and manage and are used mainly to move data (ETL), optimize the query performance etc.  They save the development time of developer when you know them inside and out. I have covered several tips and types of Materialized View in my blog if you are interested, just go to search bar on the upper right corner.